How to do cummulative sum based on group and date(YTD)..

[Anonymous]

How to do cummulative sum based on group and date(YTD) using Dax.

for example : i have column yearmonth ,category ,headcount and want result as Cummulative values based on Category and YearMonth (YTD) not only previous month.I have created measure Measure 2:=CALCULATE(sumx(FILTER(Sheet1,Sheet1[YearMonth]<= max(Sheet1[YearMonth])),[Headcountmeasure])) but its not doing cummulative sum as expected.

Measure 3:=CALCULATE(SUMX(ALLEXCEPT(Sheet1,Sheet1[Category]),Measure 2)) is only summing c1 sum (201810 ,201811).

Please help on this.

YearMonth	Category	headcount	Expected Cummulative result	formula its should use
201810	C1	20	20	201810 +Previous month values starting from Jan 2018
201810	C2	30	30	201810+Previous month values starting from Jan 2018
201810	C3	40	40	201810+Previous month values starting from Jan 2018
201810	C4	50	50	201810+Previous month values starting from Jan 2018
201810	C5	60	60	201810+Previous month values starting from Jan 2018
201810	C6	70	70	201810+Previous month values starting from Jan 2018
201811	C1	30	50	C1 201810 + c1  201811
201811	C2	40	70	C2 201810 + c2  201811
201811	C3	50	90	C3 201810 + c3  201811
201811	C4	60	110	C4 201810 + c4  201811
201811	C5	70	130	C5 201810 + c5  201811
201811	C6	80	150	C6 201810 + c6  201811

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to below measure:

Measure 4 =
CALCULATE (
    SUM ( Sheet1[headcount] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[Category] ),
        Sheet1[YearMonth] <= MAX ( Sheet1[YearMonth] )
    )
)

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please refer to below measure:

Measure 4 =
CALCULATE (
    SUM ( Sheet1[headcount] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[Category] ),
        Sheet1[YearMonth] <= MAX ( Sheet1[YearMonth] )
    )
)

Best regards,

Yuliana Gu

[Anonymous · Accepted solution]

Thanks Yuliana for the solution.I also tried Measure 2:=CALCULATE(sumx(FILTER(ALL(Sheet1[YearMonth]),Sheet1[YearMonth]<= max(Sheet1[YearMonth]) ),[Headcountmeasure]),VALUES(Sheet1[Category])) ...its giving the same output.. but i didnt understand how the context is working in this formula.