cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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!

View solution in original post

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

View solution in original post

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!

View solution in original post

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

View solution in original post

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 381 members 3,725 guests
Please welcome our newest community members: