Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need help on a very common DAX difficulty, I assume : "correct Subtotal of measure". I found the response for a measure with a SUM, something like (M-4:=SUMX(SUMMARIZE('FactTable';[Cat]);[SUM]). But in my case, I need a cumalative SUM over months, SUM of Qty that adds up over months and years. Also Year total should include previous years.
Fact table :
Cat | Qty | Date |
A | 1 | 01/07/2023 |
A | 1 | 01/08/2023 |
A | 1 | 01/09/2023 |
A | 1 | 2/1/2024 |
Expected result:
Row Labels | Qty Expected |
2023 | |
juillet | 1 |
août | 2 |
septembre | 3 |
octobre | 3 |
novembre | 3 |
décembre | 3 |
2023 Total | 15 |
2024 | 47 |
2025 | 48 |
Grand Total | 110 |
Actual result with my wrong expression
Row Labels | MonthCum | Qty 12 |
2023 | ||
juillet | 1 | 1 |
août | 2 | 2 |
septembre | 3 | 3 |
octobre | 3 | 3 |
novembre | 3 | 3 |
décembre | 3 | 3 |
2023 Total | 3 | 3 |
2024 | ||
janvier | 3 | 3 |
février | 4 | 4 |
mars | 4 | 4 |
avril | 4 | 4 |
mai | 4 | 4 |
juin | 4 | 4 |
juillet | 4 | 4 |
août | 4 | 4 |
septembre | 4 | 4 |
octobre | 4 | 4 |
novembre | 4 | 4 |
décembre | 4 | 4 |
2024 Total | 4 | 4 |
2025 | 4 | 4 |
Grand Total | 4 | 4 |
The Measure that causes me problem :
Qty 12:=VAR MonthCum =
CALCULATE(
SUM('TEST'[Qté]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
VAR AnnualCum = SUMX(
SUMMARIZE(
'Calendar';
"EndOfMonthDate"; EOMONTH(MAX('Calendar'[Date]); 0)
);
CALCULATE(
SUM('TEST'[Qté]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date] <= MAX('Calendar'[Date])
)))
RETURN
IF(ISFILTERED('Calendar'[Month]);MonthCum;AnnualCum)
Any hint or tip, tutorial or remark would be highly appreciated, I wouldn't dare to tell how many hours I've been searching, also with ChatGPT,
;- ) so I'm here back among human for help 😉
Solved! Go to Solution.
Hello,
The data set above fully covered my issue. Other years aren't important.
However meanwhile I found the right formula which is :
Coût cumulé:=VAR StartDate = DATE(2023; 7; 1)
VAR Cumul= CALCULATE(sum(VolumeUODates[Total]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date]<= MAX('Calendar'[Date])
)
)
VAR AnnualCum =
CALCULATE(
SUMX(VALUES('Calendar'[Month]); [MonthTotal]);
DATESBETWEEN('Calendar'[Date]; StartDate; EOMONTH(MAX('Calendar'[Date]); 0))
)
RETURN
IF(ISFILTERED('Calendar'[Month]); Cumul; AnnualCum)
with the help of youtube and ChatGPT, ... part human part AI makes en efficient mix.
Thank you
Hello,
The data set above fully covered my issue. Other years aren't important.
However meanwhile I found the right formula which is :
Coût cumulé:=VAR StartDate = DATE(2023; 7; 1)
VAR Cumul= CALCULATE(sum(VolumeUODates[Total]);
FILTER(
ALL('Calendar'[Date]);
'Calendar'[Date]<= MAX('Calendar'[Date])
)
)
VAR AnnualCum =
CALCULATE(
SUMX(VALUES('Calendar'[Month]); [MonthTotal]);
DATESBETWEEN('Calendar'[Date]; StartDate; EOMONTH(MAX('Calendar'[Date]); 0))
)
RETURN
IF(ISFILTERED('Calendar'[Month]); Cumul; AnnualCum)
with the help of youtube and ChatGPT, ... part human part AI makes en efficient mix.
Thank you
Please provide sanitized sample data that fully covers your issue. Including the other years. (or indicate that these are not important)
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |