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

Cumulative total as continuous line on graph

I am trying to show the cumulative total of revenue as a continuous line on a graph, This is the formula I am using for my cumulative total:

 

Cumulative Revenue = CALCULATE(SUM('Invoice by Task and Date'[Actual Revenue]), Filter(ALLSELECTED('Invoice by Task and Date'), 'Invoice by Task and Date'[Gl_Date]<=MAX('Invoice by Task and Date'[Gl_Date])))

 

But then when I graph it against my Calendar (one I built that shows project life by month), if there is no invoice data for a particular month, the cumulative total line has a gap in it as shown below. I want a continuous line from the first invoice through the last.

 

Cumulative Total Graph.PNG 

 

Any ideas how to make the cumulative revenue line continuous? The cost works because there is data for each month. Any help is appreciated.

1 ACCEPTED SOLUTION
scottsen
Memorable Member
Memorable Member

You need to use the actual calendar table in your measure as well. 

 

Cumulative Revenue = CALCULATE(SUM('Invoice by Task and Date'[Actual Revenue]), Filter(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])))

View solution in original post

6 REPLIES 6
scottsen
Memorable Member
Memorable Member

This is one of the reasons you really want to create a true and separate "Calendar Table" or "Date Table", then create a relationship between the calendar and your GL.   Your calendar will have all dates, so it will magically work out...

elcro
Frequent Visitor

I do have a completely separate Calendar Table, and there is a relationship between the two using the date of each invoice. But even with that I cannot figure out how to show the cumulation during the months when there was no invoice. Is there a different formula that works better? Thank you for the help

scottsen
Memorable Member
Memorable Member

You need to use the actual calendar table in your measure as well. 

 

Cumulative Revenue = CALCULATE(SUM('Invoice by Task and Date'[Actual Revenue]), Filter(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])))

View solution in original post

elcro
Frequent Visitor

Okay so that gets rid of the gaps (thanks!) but now it runs continuous to the end of my Calendar rather than stopping at the date of the lasy invoice. Any further advice?

 

Revenue.PNG

scottsen
Memorable Member
Memorable Member

If you have some way to trim your calendar days to the end of your sales data, that is best.

Otherwise you will need to filter your cumulative total measure to dates <= "last sale date" which you could write in a separate measure.
elcro
Frequent Visitor

thank you so much!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors