Forum Discussion

elcro's avatar
elcro
Frequent Visitor
10 years ago
Solved

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.

 

 

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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])))

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      elcro
      Frequent Visitor
      10 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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])))