Forum Discussion

Paulzz's avatar
Paulzz
Regular Visitor
9 years ago
Solved

Cumulative sum pattern

I've attempted to follow the Ferrari / Russo DAX pattern for cumulative sum, where they suggest (based on their own sample data):

 

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

 

The problem is that, when you use a slicer from the Date table (after joining it to Transactions) to filter a matrix visualisation down to 2013 only, the Cumulative Quantity measure is bringing through the SUM(Transactions[Quantity]) for all of the previous year and adding the 2012 amount to Jan 2013.  This is shown in the two images below, showing before and after filtering.  How can I change the above DAX so that Jan 2013 will show 3 for the Cumulative Quantity measure, rather than 5 (carrying on the 2012 total)?  I need a way for the cumulative measure to effectively start from what is sliced, not the whole Date table.  I've tried ALLSELECTED and other things -- I've got a feeling there's a second FILTER statement you could add to the second CALCULATE to do it, but I've had no luck so far.

 

 

 

 

  • MFelix

    ALLSELECTED shall work. Try

     

    Cumulative Quantity = 
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Transactions[Date] ), ALLSELECTED ( Transactions ) ),
        CALCULATE (
            SUM ( Transactions[Quantity] ),
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
    )
    

     

    Check a demo in the attached pbix.

3 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee

    MFelix

    ALLSELECTED shall work. Try

     

    Cumulative Quantity = 
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( Transactions[Date] ), ALLSELECTED ( Transactions ) ),
        CALCULATE (
            SUM ( Transactions[Quantity] ),
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
    )
    

     

    Check a demo in the attached pbix.

    • Paulzz's avatar
      Paulzz
      Regular Visitor

      Thanks for the quick response on that; my problem was omitting ALLSELECTED from the first part of the formula before the 2nd CALCULATE.  Nice work.

  • Hi Paulzz,

    Try to use ALLSELECTED (Transactions) instead pf ALL (Transactions) i'm not abble to test this right now but should filter your table only for the selected period.

    Regards,
    MFelix