How do I add a starting balance to a running total using DAX?

Question

I need to add a starting balance to a working, running total. The measure that works was built using the Running Total Quick Measure from PBI:

> tran_amnt running total in Date =  CALCULATE (
>     SUM ( 'History_Pivot'[tran_amnt] ),
>     FILTER (
>         ALLSELECTED ( 'calendar_reporting'[Date] ),
>         ISONORAFTER (
>                 'calendar_reporting'[Date], MAX ( 'calendar_reporting'[Date] ), DESC
>         )
>     ),
>     History_Pivot[tran_type] in {"exp","rev"}
> 
> )
>     + 0

When I chart this out, I get a perfect running total. However, this starts the values from 0, and I need to include a starting value from my table. This is another History_Pivot[tran_type] = "bal". I have a balance record for each fund/department for each year of the table, so I only want to include the "bal" on the first year/date of the running total.
I've tried adding an additional IF filter to the Calculate, but that overrides the running subtotal value.

score 0 · Answer 1 · answered Jul 24 '18 at 23:31

I would copy the entire CALCULATE statement and paste it to replace the "0" at the very end. Within that section you just need to replace the ISONORAFTER function with something that gives you that first year/date, and edit the tran_type list to "bal".

How do I add a starting balance to a running total using DAX?

1 Answers1