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
SJCee
Frequent Visitor

Stacked Area Chart using cumulative total across multiple categories

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?

1 ACCEPTED SOLUTION
LQuedas
Resolver II
Resolver II

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])

    LQuedas_0-1675351790348.png

Hope it works for you.

Cheers, LQ

View solution in original post

6 REPLIES 6
LQuedas
Resolver II
Resolver II

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])

    LQuedas_0-1675351790348.png

Hope it works for you.

Cheers, LQ

SJCee
Frequent Visitor

@LQuedas any advice?

LQuedas
Resolver II
Resolver II

Hey @SJCee ,

 

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

 

the sample table I used to simulate your problem was:

LQuedas_0-1675165648158.png

 

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

LQuedas_1-1675165680361.png

 

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

LQuedas_2-1675165714340.png

 

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

LQuedas_3-1675165828178.png

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

 

Hope this helps.

 

Cheers, LQ

 

SJCee
Frequent Visitor

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
Frequent Visitor

Hi @LQuedas ,

 

Thank you very much for your response.

 

This what the chart looks like:

Chart.PNG

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
Resolver II
Resolver II

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

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.