Forum Discussion
Help with Running Total
I can't seem to figure out how to write DAX to calculate what's in column Running Balance. The Reserve Beginning Balance is what starts the calculation on a year basis and each month Reserve is added to it.
Reserve Beginning Balance + Reserve = Running Balance.
Any ideas how I would calculate Running Balance?
yearMonth Reserve Reserve Balance YTD Reserve Beginning Balance Running Balance
3/1/2017 ($11,100) $107,000.00 $0.00 ($1,457,600.00)
2/1/2017 $63,600 $118,100.00 $0.00 ($1,382,900.00)
1/1/2017 $54,500 ($1,446,500.00) ($1,501,000.00) ($1,446,500.00)
I can't understand why the Running Balance for 3/1 is ($1,457,600.00). Based on your logic, it should be a cumulative sum for those two columns, you just need to create a YTD measure.
Running Balance = CALCULATE ( SUM ( Table1[Reserve Beginning Balance] ) + SUM ( Table1[Reserve] ), FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) && Table1[Date] >= DATE ( YEAR ( MAX ( Table1[Date] ) ), 1, 1 ) ) )Regards,
3 Replies
I can't understand why the Running Balance for 3/1 is ($1,457,600.00). Based on your logic, it should be a cumulative sum for those two columns, you just need to create a YTD measure.
Running Balance = CALCULATE ( SUM ( Table1[Reserve Beginning Balance] ) + SUM ( Table1[Reserve] ), FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) && Table1[Date] >= DATE ( YEAR ( MAX ( Table1[Date] ) ), 1, 1 ) ) )Regards,
It would seem that you could just create a measure that was a running total of Reserve and then simply add that to your Reserve Beginning Balance of your MIN date?
