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.

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.