Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help needed: Rolling Average of a Indexed Value

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

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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
Not applicable

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
Not applicable

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

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
Not applicable

Hey!

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.