Stacked Area Chart using cumulative total across multiple categories

[SJCee]

I have built the following DAX formula that succesfully provides a cumulative total value by category:

"CALCULATE(
SUM('Table'[Value]),
FILTER(ALL('Table'),
'Table'[Date]<=MAX('Table'[Date])),
VALUES('Table'[Category])
)"

The issue I have is each category has a different MAX date, therefore in the Area Chart the cumulative total drops if one catgeory has a MAX date higher than another.

Any ideas how to get round this?

[LQuedas · Accepted solution]

hey @SJCee ,

you need first to create the cumulative measure like, (using the sample model I used):

CumulativeValue =

CALCULATE (

    sum(Table_1[Value]),

    FILTER (

        ALL (Table1_Dates[Date] ),

        Table1_Dates[Date] <= MAX ( ( Table1_Dates[Date]) )

    )

)

then use that new cumulative measure in the previous formula

New Measure =

VAR _Date = SELECTEDVALUE(Table1_Dates[Date])

VAR _Last = CALCULATE(LASTNONBLANKVALUE(Table1_Dates, [CumulativeValue]), Table1_Dates[Date]<_Date)

return IF([CumulativeValue] = BLANK(),_last,[CumulativeValue])

   

Hope it works for you.

Cheers, LQ

[SJCee]

@LQuedas any advice?

[LQuedas]

Hey @SJCee ,

I've a solution to your problem,  hope it works for you

the sample table I used to simulate your problem was:

1) Create a Date Table (if you don't have one):

2) create a relationship between the original table and the new date table

Create a new meeasure like:

New Measure =

VAR _Date =
    SELECTEDVALUE ( Table1_Dates[Date] )
    
VAR _Last =
    CALCULATE (
        LASTNONBLANKVALUE ( Table1_Dates, SUM ( Table_1[Value] ) ),
        Table1_Dates[Date] < _Date        
    )
    
RETURN
    IF ( SUM ( Table_1[Value] ) = BLANK (), _last, SUM ( Table_1[Value] ) )

with this code your visual will looks like

the future dates will be filled in with the last non empty value.

Hope this helps.

Cheers, LQ

[SJCee]

Thank you @LQuedas, I think it is almost there, just wondering how I would edit your formula in order to make it cumulative?

Sam

[SJCee]

Hi @LQuedas ,

Thank you very much for your response.

This what the chart looks like:

As you can see the chart works perfectly until around 2027. The reason is the MAX date in the three categories differs e.g. dark blue max date is in 2027, light blue in 2028 and orange in 2031. Is there a way I can edit the formula so that three categories are using the same max date?

Thanks,

Sam

[LQuedas]

Hey @SJCee ,

Can you screenshot the visual showing the issue? I did some tests here and your formula seems work fine... so I need to understand the visual configuration.

Cheers, LQ