Moving Average for the last x dates available

[veranceftw]

Hello, I am trying to create a metric that based on the selected `player_id` it returns the moving average for the last x dates available.

rolling_tst =

VAR player = SELECTEDVALUE(Players[player_id])

VAR Result =

CALCULATE (

AVERAGEX (

VALUES ( player_stats_matchday[Date] ),

[xGA Net]

),

FILTER(Players, Players[player_id] = player)

)

Return

Result

I tried to create a new measure like the one above but it is not working. Any idea how to pass the last 5 dates to the rolling average? 

[Anonymous · Accepted solution]

Hi @veranceftw ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a measure as below to get the count of dates per player

CountofDates = 
CALCULATE (
    DISTINCTCOUNT ( 'player_stats_matchday'[Date] ),
    FILTER (
        ALLSELECTED ( 'player_stats_matchday' ),
        'player_stats_matchday'[player_id] = SELECTEDVALUE ( 'Players'[player_id] )
            && 'player_stats_matchday'[Date] <= SELECTEDVALUE ( 'player_stats_matchday'[Date] )
    )
)

2. Create a measure as below to get the rolling_tst

rolling_tst =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'player_stats_matchday'[Date] ),
        FILTER (
            ALLSELECTED ( 'player_stats_matchday' ),
            'player_stats_matchday'[player_id] = SELECTEDVALUE ( 'Players'[player_id] )
                && 'player_stats_matchday'[Date] <= SELECTEDVALUE ( 'player_stats_matchday'[Date] )
        )
    )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( 'player_stats_matchday' ),
            [CountofDates] >= _count - 4
                && 'player_stats_matchday'[CountofDates] <= _count
        ),
        'player_stats_matchday'[xG Net]
    )

If the above one is not what you want, please provide some sample data with Text format(exclude sensitive data) and your expected result with more details(backend logic, special examples with screenshot etc.). Thank you.

Best Regards