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

Cumulative Total

Hy,

 

I had an issue with a latest project for my company. The task was to show the liquidity ratio per month (filtered by the chart of account Id). A formula for calculating the cumulative total was used. I used many variations of the cumulative totals formula which are mentioned even here on the PowerBI community pages. One of them is this one for example:

 

Cumulative = IF(COUNTROWS(GeneralLedger) > 0, CALCULATE([LiquidityRatio],FILTER(ALL(GeneralLedger[Date]), GeneralLedger[Date]<= MAX(GeneralLedger[Date]))),BLANK())

 

The filtering of the chart account id was done in the Filters option.

The issue appears when I present the results with a line chart or a area chart. When I use a waterfall chart, the results are suming up as they should, but the line and area chart do not present the data in the desired way. Here is what happens:

 

C1.PNG

 

 

This is an example with a modified data set where just three months are used, April with a liquidity ratio of $632K, May with $17K and june with a negative of -$4.733. As you can see the values are not sumed up (the line is not rising) they are just presented as an apsolute value per month.

Any ideas why is this happening that way?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Cumulative Total

Hi @Chorri21,

 

I found your measure worked for me.  But I was only working with a very small dataset.

 

Maybe check your GeneralLedger[Date] column is a date time.  Make sure you have the latest version of Power BI.

 

If that doesn't work, perhaps look to use a date/calendar table to build your measure over.

 

In any case here is the super simple version using your code.

 

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Chorri21 Frequent Visitor
Frequent Visitor

Re: Cumulative Total

Thank you @Phil_Seamark for your replay. Well the issue was in the date format which was imported from the server. There was that option GeneralLedger[Date].[Date] which I had to specify in the code and it has worked well.

Greetings

3 REPLIES 3
Phil_Seamark Super Contributor
Super Contributor

Re: Cumulative Total

Hi @Chorri21,

 

I found your measure worked for me.  But I was only working with a very small dataset.

 

Maybe check your GeneralLedger[Date] column is a date time.  Make sure you have the latest version of Power BI.

 

If that doesn't work, perhaps look to use a date/calendar table to build your measure over.

 

In any case here is the super simple version using your code.

 

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestacc...


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Chorri21 Frequent Visitor
Frequent Visitor

Re: Cumulative Total

Thank you @Phil_Seamark for your replay. Well the issue was in the date format which was imported from the server. There was that option GeneralLedger[Date].[Date] which I had to specify in the code and it has worked well.

Greetings

Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Cumulative Total

Nice work!  Well done.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 245 members 2,669 guests
Please welcome our newest community members: