I've been stuck on this little problem that I'm sure has a simple solution to it, however I just can't seem to crack it.
I have a date table, a budgets table and a table that contains transactions that are assigned to a budget. Here is some example data to see what I mean:
Date // Budget // Cost
01 May 2015 // Budget A // -52
01 May 2015 // Budget B // -100
04 May 2015 // Budget A // 54.26
07 May 2015 // Budget C // -86.2
07 May 2015 // Budget C // -426.85
14 May 2015 // Budget A // 595.23
18 May 2015 // Budget D // -65.75
21 May 2015 // Budget A // -139.87
Budget // Initial Balance
Budget A // 10000
Budget B // 8500
Budget C // 19000
Budget D // 5000
What I'm trying to do is to show what the current balance is of each budget, based on the transactions that have taken place. (i.e. the initial balance + sum of transactions for that budget.
Where I'm coming unstuck is that I need to include a date slicer but the calculated budget balances needs to show the balance of each budget at the end of the period - not just the transactions within the date range from the slicer (the transactions date back to May 2012)
So for example, if the date slicer filtered data between 01 October 2016 and 31 October 2016, I'd need to know what the current balance of each budget is as of 31 October 2016. Whenever I try this with a measure, I end up getting the balances based on either the transactions between 01 and 31 October, or based on ALL of the transactions (up to today).
So the trouble I'm having is trying to sum the transactions for the right date range. This is my current DAX but this only works if the date slicer starts on 01 May 2015. I'd like the measure to ignore the lower date of the slicer.
Budget Balance = CALCULATE (
'Date'[Date] <= MAX ('Date'[Date])
Any ideas? I'm sure it's just using the right combination of calculate, sum, filter and all - but I can't get it to work!