Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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.
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
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.