topic Cumulative Total for Financial Years only in DAX Commands and Tips

Cumulative Total for Financial Years only

Anonymous — Fri, 03 Dec 2021 07:05:25 GMT

Hi there,
I am trying to calculate the cumulative cash outflows or cash inflows. And I dont have a data table for this as the data are only on financial year end, i.e. 30/6/20XX. (Please ref to the 3rd screenshot)

I tried to use the following code but seems not working. Please note the table name is TEST and DIM_Period is my period dimension table which is not in days.

This is my period table.

Please help 😄 Truly appreciate!

Re: Cumulative Total for Financial Years only

bcdobbs — Fri, 03 Dec 2021 07:28:44 GMT

Hi,

Once you load the sum into the variable net_cash it becomes fixed and is not affected by your later calculate.

If you put that code directly in to the calculate it will get further than before. Or you could define a separate measure called net cash and use that inside the calculate.

Re: Cumulative Total for Financial Years only

colacan — Fri, 03 Dec 2021 07:27:54 GMT

Anonymous Hi, If you use VAR as expression of calculate, it would not work as you intended becuase VAR work like constant when one declares it. In other words, in your formular, when you declare net_cash, it became constant number (the total of cash_inflows). so it return always the same number regardless of modificatio of CALCULATE. and it applies to Las visible period as well.

To resolve your issue, you should create measures for net_cash & Las visible period or dont use VAR (use the formular derectly inside CALCULATE)

Hope this helps you.

Re: Cumulative Total for Financial Years only

Anonymous — Mon, 06 Dec 2021 00:11:17 GMT

Thanks Colacan. This is really helpful. I will have a go.

Re: Cumulative Total for Financial Years only

Anonymous — Mon, 06 Dec 2021 00:31:13 GMT

Hi Colacan,

I had created measures for net_cash & LastVisible Period separately as below. However it seems not working. I suspect if this is something to do with my Period table?

Re: Cumulative Total for Financial Years only

smpa01 — Mon, 06 Dec 2021 01:10:28 GMT

Anonymous Can you provide some sample data?

Re: Cumulative Total for Financial Years only

Anonymous — Mon, 06 Dec 2021 02:45:38 GMT

The data that I am using is the one in the purple table in my question.