Forum Discussion
Cumulative total as continuous line on graph
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])))
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...
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
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])))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?
- 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.