Running Sum with Stacked Area chart ?

[Cookistador]

Hello Everyone,

I have to build a report and embedd this report on our company website
So I discovered yesterday that publish to the web was not supported...

I have to build a running sum, with the help of this wonderfull community I was able to do that, but I still have a last issue
This is the measure I used for my running sum:

   VAR CurrentDate = MAX('Date'[Date])

   VAR RunningSum =

   CALCULATE(

       SUMX(

           FILTER(

               ALLSELECTED('Date'[Date]),

               'Date'[Date] <= CurrentDate

           ),

           [AverageValue]  -- Using the existing measure called Value

       ))

RETURN

IF(RunningSum<=0,BLANK(),RunningSum)

And my AverageValue is calculated with the following calcullation:

    AVERAGEX(

        SUMMARIZE(

            Data,

            Data[Id],

            Data[Region],

            "AvgAmount", AVERAGE(Data[Amount])*AVERAGE(Data[Entity])

        ),

        [AvgAmount]

    )

For this part, it is working, I get the following value 

But it is very slow, the issue is comming from the fact that is making a calcuation for each day my date table
If I let my mouse on the chart where I have no chage, it is returning a value:

What I would like to achieve, it to get the same chart, but only reutning the calculation for the value when there is a change
(when there is an increase or decrease) 
I try to use the Date from my Data table, but it is returning something totally wrong (with the Date dimension, it is very slow, I guess it is because it has to calculate points for each date, with data table, it took less than 1s to show this weird chart)

Once the value reaches its max, it is dissapearing

Any idea of what I'm doing wrong?

[TomMartens · Accepted solution]

Hey @Cookistador ,

I suggest this measure that is more simple and probably a little faster:

SalesAmount (running) = 
var currentdDateKey = CALCULATE( MAX( 'DimDate'[Datekey] ) )
return
IF( NOT( ISBLANK( [SalesAmount (ms)] ) ),
    CALCULATE(
        [SalesAmount (ms)]
        , 'DimDate'[Datekey] <= currentdDateKey
    ),
    BLANK()
)

Of course, it's the same approach (i call this approach "sanity check"), checking if he current date (being pecised the max value based on the datekey granularity) has a value if not, do not create a running sum. This will avoid the "unnecessary calculations" while doing a "running something":

But then, you have to be prepared that your chart will look like this:

This happens because a line is not "projected" when there is no value. for this reason, you probably have to adapt the "sanity check" and the grain of your x-axis.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[rajendraongole1]

Hi @Cookistador -Instead of using your full date table, which results in calculations for each day, restrict the calculation to dates with non-blank values for AverageValue.

create a below running sum measure:

VAR CurrentDate = MAX('Date'[Date])
VAR RunningSum =
CALCULATE(
SUMX(
FILTER(
ALLSELECTED('Date'[Date]),
'Date'[Date] <= CurrentDate &&
NOT(ISBLANK([AverageValue])) -- Only include dates with non-blank values
),
[AverageValue]
)
)
RETURN
IF(RunningSum <= 0, BLANK(), RunningSum)

To further improve performance, you can limit the calculation to a rolling window (e.g., the last 12 months) if a cumulative view of all historical data isn’t necessary. You can apply this condition in the FILTER section of your measure to restrict the calculation to the most recent dates.