topic Re: Help needed: Rolling Average of a Indexed Value in DAX Commands and Tips

Help needed: Rolling Average of a Indexed Value

Anonymous — Tue, 21 Nov 2023 07:42:32 GMT

Hey!

I have been struggling with this problem for a while. I would like to create a rolling average of this measure here. Basically the measure is transforming the sales to index starting from 100 for selected time period. The measure works well, but when I try to create a rolling average of it I don't get the rolling average to work. I have tried countless of options but none seems to work.

Index Sales = VAR _FirstDate = CALCULATE(MIN(vDimCalendar[Date]), ALLSELECTED(vDimCalendar[Date]) ) VAR ThisDate = SELECTEDVALUE(vDimCalendar[Date]) VAR BaseValue = CALCULATE( SUM(CubeSales[ACT]), DATEADD(vDimCalendar[Date], _FirstDate - ThisDate, DAY)) VAR CurrentValue = CALCULATE(SUM(CubeSales[ACT])) VAR IndexedValue = DIVIDE(CurrentValue, BaseValue, BLANK()) * 100 RETURN IndexedValue

Re: Help needed: Rolling Average of a Indexed Value

Anonymous — Tue, 21 Nov 2023 07:44:45 GMT

Here is something I have tried, but this does not return the correct output.

6 Months Rolling average Index = AVERAGEX( WINDOW( -6, REL, 0, REL, ORDERBY (vDimCalendar[Date], ASC) ), CALCULATE([Index Sales]) )

Re: Help needed: Rolling Average of a Indexed Value

AnalyticPulse — Tue, 21 Nov 2023 07:51:15 GMT

can you try below dax:
RollingAverageIndexedValue = VAR _WindowSize = 7 -- Adjust the window size as needed RETURN AVERAGEX( FILTER( ALLSELECTED(vDimCalendar), vDimCalendar[Date] >= MIN(vDimCalendar[Date]) - _WindowSize + 1 && vDimCalendar[Date] <= MAX(vDimCalendar[Date]) ), [IndexedValue] )

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney

Re: Help needed: Rolling Average of a Indexed Value

Anonymous — Tue, 21 Nov 2023 07:59:08 GMT

Hey!

Unfortunatelly the measure you provided does not work. It returns 100 for each date.

Re: Help needed: Rolling Average of a Indexed Value

tamerj1 — Tue, 21 Nov 2023 08:12:17 GMT

Hi Anonymous
Please try

Index Sales Running Average = VAR CurrentDate = MAX ( vDimCalendar[Date] ) VAR SelectedDates = ALLSELECTED ( vDimCalendar[Date] ) VAR BaseValue = CALCULATE ( SUM ( CubeSales[ACT] ), SelectedDates ) VAR DatesBefore = FILTER ( SelectedDates, vDimCalendar[Date] <= CurrentDate ) VAR Result = AVERAGEX ( DatesBefore, VAR ThisDate = vDimCalendar[Date] VAR CurrentValue = CALCULATE ( SUM ( CubeSales[ACT] ), ALL ( vDimCalendar ), vDimCalendar[Date] = ThisDate ) RETURN DIVIDE ( CurrentValue, BaseValue ) ) RETURN Result

Re: Help needed: Rolling Average of a Indexed Value

Anonymous — Mon, 27 Nov 2023 07:56:12 GMT

Hey!

This seems to return correct result. I modified it a bit since it was returning the result for whole timeframe. Also it seems to be very slow, so I might try to optimize it. Anyway thanks for help!