Cumulative Balances for balance sheet

DAX DAX Calculations
1

Hi there,

I am new to Power BI and Dax and am working through the financial reporting course. I’m up to the Balance Sheet section and have hit a road block. The example BS data in the course already includes cumulative balances and my GL data is in raw form (GL entries table from Business Central) so I need to calculate the cumulative balances at the specific reporting date. Please can someone direct me where I can find some material on how to do this please? I’ve tried googling, trawling through Youtube and tried the following measure but its not producing any results.

Measure 1: BS Values = CALCULATE(sum(‘Fact GL Data’[Amount]), ‘Fact GL Data’[GL Acc No] > 4999)

Measure 2: Running Total =

CALCULATE([BS Values],

FILTER(ALL(Dates),

    Dates[Date] >= MAX(Dates[Date]) - Dates[Date] <= MAX(Dates[Date])

    )

)

Thanks in advance.

2

Sorry, looks like I had the Running Total code incorrect,

CALCULATE([BS Values],

FILTER(ALL(Dates),

    Dates[Date] <= MAX(Dates[Date])

    )

)

The above returns the movement for the selected period only. Thanks

1 Like
3

Hi @amyballot,

Great that you can find out. If you have other problem just post to forum.

It’s great to follow the training and in parallel set up your real-life example.

Info: you can also use time intelligence formula DatesYTD (for Current Year YTD)

BS YTD Values =CALCULATE([BS Values],DatesYTD(Dates[Date]))

just check before if the Fact GL Data’[Amount] includes both Credits and Debits

  • where you would like to use it (do you need also Open Balance so you need TotalYTD or usually it is Open Balance + Current Year YTD)

More about YTD functions you can find at Mastering DAX Calculations:
https://app.enterprisedna.co/app/player-course/5?chapter=191&item=557

more about Time Intelligence functions at:
https://app.enterprisedna.co/app/player-course/5?chapter=191&item=552

Good luck with your balance sheet report set up.

1 Like
4

Thanks @mspanic for taking the time to reply!

I found a work around for this - YTD measures didn’t quite work for me as I wanted one table with accumulated balances so that the report could be rolled back if required - I am building out our monthly board pack and quite often we need to go back to prior periods for reporting in due diligence processes.

I needed 2 filters to summarise the data - by reporting category and by month and also needed to figure out how to calculate the accumulated balances. Struggled for a while to get it to work but got there in the end by creating a summary table with a calculated column with the accumulated balances.

Initially I hit a road block where if an account has no transactions in the particular reporting period then it wasn’t reporting that account in the balance sheet report (my balance sheet needs 13 months history by month in the report too).

So uploaded a “monthly” date table which contains all the months I need and then crossjoined the chart of accounts and monthly date tables and then brought in the movement from the GL entries table and calculated the accumulated balances off that.

Probably not the most efficient way of doing this, but as a newbie its worked for me.

Including the DAX below for anyone else that comes across this to be resolved:

Summary BS Crossjoin table =
CROSSJOIN(values(MonthTable[Month]),
Values(‘Fact GL Data - BS’[New Mapping]))

Crossjoin balances =
CALCULATE([Fact BS Data Actuals],
FILTER(‘Fact GL Data - BS’, ‘Fact GL Data - BS’[New Mapping] = ‘Summary BS Crossjoin table’[New Mapping]
&& ‘Summary BS Crossjoin table’[Month] = ‘Fact GL Data - BS’[Month]))

Crossjoin Accum Bal Column =
Var currentdate = ‘Summary BS Crossjoin table’[Month]
Var BSMapping = ‘Summary BS Crossjoin table’[New Mapping]
Var FilteredTable = FILTER(‘Summary BS Crossjoin table’, ‘Summary BS Crossjoin table’[Month] <= Currentdate
&& BSMapping = ‘Summary BS Crossjoin table’[New Mapping])
Return
Calculate([Sum of Crossjoin BS Balances], FilteredTable)

I’m really enjoying the resources and materials from Enterprise DNA so thank you to you all!