topic Re: Cumulative Total of Two Measures from Separate Tables in DAX Commands and Tips

Cumulative Total of Two Measures from Separate Tables

third_hicana — Tue, 21 Jan 2025 03:06:49 GMT

Hi. I just want to ask a help on calculating cumulative totals based on two measures from separate tables.

I have a data model below. What I did was, I created a measure for Table 1 and Table 2. Then I add them up by simpling doing addition Measure 1 + Measure 2. Let's call it Measure Total. Then I used the DAX below. However, I got a wrong cumulative value (see second image)

Cumulative Total =

Var maxDate = MAX(Calendar Table[Date]

Return

CALCULATE(

Measure Total,

ALL(Calendar Table),

(Calendar Table[Date] =< maxDate)))

Wrong cumulative

Thank you in advance for any help you can give.

Re: Cumulative Total of Two Measures from Separate Tables

shafiz_p — Tue, 21 Jan 2025 03:42:09 GMT

Hi third_hicana The issue with your DAX formula is, inside calculate you have used ALL function, which is removing the filter context from the Calendar, which might be causing the cumulative calculation to go wrong. You need to use ALL function inside the FILTER function to correctly evaluates cumulative totals. Try the below code:

Cumulative Total = VAR maxDate = MAX('Calendar'[Date]) RETURN CALCULATE( [Measure Total], FILTER( ALL('Calendar'), 'Calendar'[Date] <= maxDate ) )

ALL function is used within the FILTER function to remove any existing filters on the Calendar, but the FILTER function then reapplies the filter to only include dates up to maxDate.

I have tried the scenario and found the result you want. Check this:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,

Shahariar Hafiz

Re: Cumulative Total of Two Measures from Separate Tables

third_hicana — Tue, 21 Jan 2025 03:51:43 GMT

Hi shafiz_p

When I only selected 3 dates in the slicer, it does not give the cumulative of the selected beginning date up to the last selected date. For example, if I want to select from March to May, the Cumulative Total should be
March 200
April 450
May 800

Re: Cumulative Total of Two Measures from Separate Tables

third_hicana — Tue, 21 Jan 2025 03:58:52 GMT

I think ALLSELECTED will work

Cumulative Total =
VAR maxDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
[Measure Total],
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date] <= maxDate
)
)