Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative Total for Financial Years only

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)

 

RebeccaSonlit_0-1638514758112.png

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.  

 

RebeccaSonlit_1-1638514900911.png

This is my period table. 

RebeccaSonlit_2-1638515005437.png

Please help 😄 Truly appreciate!

 

 

 

 

1 ACCEPTED SOLUTION
colacan
Resolver II
Resolver II

@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.

View solution in original post

6 REPLIES 6
colacan
Resolver II
Resolver II

@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.

Anonymous
Not applicable

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?

RebeccaSonlit_0-1638750595447.png

RebeccaSonlit_1-1638750641015.png

 

 

@Anonymous  Can you provide some sample data?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

Anonymous
Not applicable

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

bcdobbs
Super User
Super User

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors