DAX to calculate cumulative sum column (year to date) for all individual products

Question

I have 3 columns in a table: Date, Product, and Volume. Based on this is I want to calculate the cumulative sum (or YTD) column for each of the products.

I know that to calculate cumulative total we use:

YTD_Actual = CALCULATE(Sum(Combined[Actual Volume]),FILTER(Combined,Combined[Date] <= EARLIER(Combined[Date])))

But I want this to additionally filter individual products and do this calculation for that product.

The expected column is shown in the picture below:

Please avoid providing sample data in images, It is much handier for people to use it when it's in text format. — Pratik Bhavsar

Pratik Bhavsar Pratik Bhavsar · Accepted Answer · 2019-06-05T11:04:33

As far as you manage to get the Month column in date format, the following works:

YTD = 
VAR currProduct = Table1[Product]
VAR currMonth = Table1[Month]
VAR ytdTotal =  
    CALCULATE(
        SUM(Table1[Volume]),
        FILTER(
           ALL(Table1),
           Table1[Month] <= currMonth &&
           Table1[Product] = currProduct
        )
    )
RETURN IF(NOT(ISBLANK(ytdTotal)), ytdTotal,0)

Result:

DAX to calculate cumulative sum column (year to date) for all individual products

1 Answers