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.

jb101's avatar
jb101
New Member
8 years ago

Thanks, this worked perfectly for what I needed.