Forum Discussion

MWitkin's avatar
MWitkin
Frequent Visitor
10 years ago
Solved

Cumulative Line Formula

Hello. I need to draw a cumulative line across this graph for both columns represented as bars in this graph visual: Both columns are date columns and are on the same table, and the bars r...
Need Help
Tutorial Requests
  • Twan's avatar
    Twan
    10 years ago

    You should be able to use the formula that Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

     

     

    Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

    Cummulative Planned Line =
    CALCULATE (
        COUNTA ( 'Table1'[Planned] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
        )
    )

     

    Here is what I generated with your example data set.

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

The general cumulative total pattern can be found here:

http://www.daxpatterns.com/cumulative-total/

 

Basically it is:

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

I would have to see your specific data, or sample data and relationships to write a specific solution or your model.

  • MWitkin's avatar
    MWitkin
    Frequent Visitor
    10 years ago

    Here a sample of the data&colon;

     

     

    The report I showed an image of has bars that count the number of dates in the "Planned" and "Actual" columns and show them in bars to show the difference in the two counts. The "StartOfMonth" column is used to group the bars together by each month in the 'Shared Axis' field of the BI report. All columns are in one table. 

     

     

    • Twan's avatar
      Twan
      Advocate IV
      10 years ago

      You should be able to use the formula that Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

       

       

      Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

      Cummulative Planned Line =
      CALCULATE (
          COUNTA ( 'Table1'[Planned] ),
          FILTER (
              ALL ( 'Table1' ),
              'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
          )
      )

       

      Here is what I generated with your example data set.

       

       

      • MWitkin's avatar
        MWitkin
        Frequent Visitor
        10 years ago

        I implemented the formulas suggested above, but the numbers illustrated don't seem to match up to the numbers on the bars.

         

        You can see the line value at the highlighted point is 8,370 while the bars is hovers by are both only showing a value of 15. It seems to just be doubling it's count of every item in the data set (there are about 550 total items in the data set by their dates span over a long period of time).

         

        Did I miss a step?

         

         

        Judging by the image Twan showed of his solution, that formula should work perfectly.

  • jb101's avatar
    jb101
    New Member
    8 years ago

    Thanks, this worked perfectly for what I needed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I used the formula but it is not limiting based on the Visual Filter.

     

    Here is my formula:

     

    Rev_Local_Cummulative =
    CALCULATE (

    _Revenue[Revenue_LocalCurrency] ,

    FILTER (
    ALL ( DateInvoice[CalendarDate] ),
    'DateInvoice'[CalendarDate] <= MAX ( 'DateInvoice'[CalendarDate] )
    )
    )

    I have a Visualization Filter set to June 2018. June 1 starts out with the sum of all prior data and then increments by the current filtered month daily amount.