Forum Discussion

SJCee's avatar
SJCee
Frequent Visitor
3 years ago
Solved

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?

Need Help
  • LQuedas's avatar
    LQuedas
    3 years ago

    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

6 Replies

  • LQuedas's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    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

  • SJCee's avatar
    SJCee
    Frequent Visitor
    3 years ago

    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's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    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's avatar
      SJCee
      Frequent Visitor
      3 years ago

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

       

      Sam

  • LQuedas's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    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