cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RebeccaSonlit
Frequent Visitor

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

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

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

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

 

 

@RebeccaSonlit  Can you provide some sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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

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
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors