Help needed: Rolling Average of a Indexed Value

[Anonymous]

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

[tamerj1 · Accepted solution]

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

[Anonymous]

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! 

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 ) * 1000
    )
RETURN
    IF(CurrentDate <= MAX(CubeSales[Date]),
    Result,
    BLANK())

[Anonymous]

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

[AnalyticPulse]

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

[Anonymous]

Hey!

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