Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tristan75
Regular Visitor

Total and Subtotal of cumulative sum over months and years

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 : 

CatQtyDate
A101/07/2023
A101/08/2023
A101/09/2023
A12/1/2024


Expected result: 

Row LabelsQty Expected
2023 
juillet1
août2
septembre3
octobre3
novembre3
décembre3
2023 Total15
202447
202548
Grand Total110

 

Actual result  with my wrong expression

Row LabelsMonthCumQty 12
2023  
juillet11
août22
septembre33
octobre33
novembre33
décembre33
2023 Total33
2024  
janvier33
février44
mars44
avril44
mai44
juin44
juillet44
août44
septembre44
octobre44
novembre44
décembre44
2024 Total44
202544
Grand Total44

 

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 😉

1 ACCEPTED SOLUTION
Tristan75
Regular Visitor

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

View solution in original post

2 REPLIES 2
Tristan75
Regular Visitor

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

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. Including the other years.  (or indicate that these are not important)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.