Cumulative Total of Two Measures from Separate Tables

[third_hicana]

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.

[shafiz_p · Accepted solution]

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

[shafiz_p · Accepted solution]

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

[third_hicana]

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

[third_hicana · Accepted solution]

I think ALLSELECTED will work

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