Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |