DAX Formula to calculate Cumulative Totals with ClosingBalanceYear Measure

Hi, I am trying to calculate cumulative totals with my ClosingBalanceYear measure. Please see screenshot below of table. The “Running Total Measure2” column is my ClosingBalanceYear measure and I need it to add cumulative from FY13 onwards.
image

I am using a Date table as shown in the Enterprise DNA and all other transactions work it is just this one particular one that is still stopping me.

Thanks you for your help in advance.

hi,

Can you share the measure formula you have used and the pbix used if possible?

Hi baijumohan,

I unfortunately can’t send the pbix, but the measure I used for the Column “Running Total Measure2” is
Running Total Measure2 =
OPENINGBALANCEYEAR([Net Profit for the Period],Dates[Date],ALL(‘Actual Data’[TxDate]),“30/09”)

The measure I used for the Column “Net Profit for the Period” is
Net Profit for the Period =
TOTALYTD ( [Total Nett Profit], Dates[Date], ALL(Dates[Date]), “30/09”)

image

What I did try to do is to calculate the Closing Balance of FY12 as the Balance for the whole of FY13 and that I got working with the column “Running Total Measure2” then what I need to do is to have cumulative totals for the column “Running Total Measure2” from FY13 onwards.

Is that possible?

Thank you for your help in advance.

Running Total Measure2 measure should dynamically calculate the dynamic opening balance for each financial year. I think the issue is in the measure you are hard-coding the end date “30/09” . Can you try to use below instead Max(Dates[Date])

You should be able to solve this relatively easily using the standard cumulative total pattern.

Check out these resources below if you still need help with this.

Sam