Cumulative sum pattern

[Paulzz]

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.

[Eric_Zhang · Accepted solution]

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

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.

[MFelix]

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