Measure Moving Average last 5 values

[seanmcc]

Hello,

I have a table with values (eg. Eccentric Duration [ms]) for many players over a number of dates. There will not always be a value for every day.

I need to create a measure that calculates the rolling average of the most recent 5 values per player.

In my report I will want to use a slicer for Name and Season.

If there is less than 5 values filtered then average values available.

Name	Season	Date	Trial	Eccentric Duration [ms]	Rolling Average of 5
Player 1	2020/21	17/08/2020	Mean	337
Player 1	2020/21	22/08/2020	Mean	372	354.5
Player 1	2020/21	07/10/2020	Mean	356	355
Player 1	2020/21	02/11/2020	Mean	338	350.75
Player 1	2020/21	11/11/2020	Mean	395	359.6
Player 1	2020/21	07/02/2021	Mean	353	362.8
Player 1	2021/22	29/06/2021	Mean	380	364.4
Player 1	2021/22	05/07/2021	Mean	393	371.8
Player 1	2021/22	22/07/2021	Mean	421	388
Player 2	2020/21	17/08/2020	Mean	510
Player 2	2020/21	22/08/2020	Mean	477	493.5
Player 2	2020/21	07/10/2020	Mean	971	652.7
Player 2	2020/21	13/10/2020	Mean	488	611.5
Player 2	2020/21	04/06/2021	Mean	722	633.6
Player 2	2021/22	29/06/2021	Mean	491	629.8
Player 2	2021/22	05/07/2021	Mean	465	627.4
Player 2	2021/22	09/07/2021	Mean	483	529.8
Player 2	2021/22	22/07/2021	Mean	491	530
Player 3	2020/21	17/08/2020	Mean	581
Player 3	2020/21	22/08/2020	Mean	483	532
Player 3	2020/21	08/09/2020	Mean	581	548.3
Player 3	2020/21	29/09/2020	Mean	587	558
Player 3	2020/21	20/10/2020	Mean	505	547.4
Player 3	2020/21	11/11/2020	Mean	471	525.4
Player 3	2020/21	23/03/2021	Mean	556	540
Player 3	2021/22	29/06/2021	Mean	500	523.8
Player 3	2021/22	05/07/2021	Mean	513	509

I hope I have explained this well enough.

Thanks in advance.

Sean

[Jihwan_Kim]

Eccentric duration measure : =

SUM ( Data[Eccentric Duration [ms]]] )

Rolling avg of last five : =

VAR _topnnumber = 5

VAR _currentplayer =

MAX ( Players[Name] )

VAR _currentdate =

MAX ( Dates[Date] )

VAR _tableperplayer =

FILTER (

ALLSELECTED( Data ),

Data[Name] = _currentplayer

&& Data[Date] <= _currentdate

)

VAR _topNtable =

TOPN ( _topnnumber, _tableperplayer, CALCULATE ( MAX ( Data[Date] ) ), DESC )

VAR _result =

AVERAGEX ( _topNtable, [Eccentric duration measure :] )

RETURN

IF ( NOT ISBLANK ( [Eccentric duration measure :] ), _result )

Link to the sample PBIX file 

[seanmcc]

Hi @Jihwan_Kim 

Thanks for the reply.

I have tried your solution but am coming up with blank cells.

I have used Player No to anonymise names. Perhaps this is causing issues?

Please see below the measures I have used. I am unsure where I am going wrong.

I have used average in my measure, as in my full data set there would be more than one 'trial, (eg. mean, trial1, trial2, trial3 etc.) per date per player.

Eccentric duration measure: = AVERAGE( 'CMJ PowerBI (2)'[Eccentric Duration [ms]]] )

Rolling avg of last five: =

VAR _topnnumber = 5

VAR _currentplayer =

MAX ( 'Players'[Player No] )

VAR _currentdate =

MAX ( 'Calendar'[Date] )

VAR _tableperplayer =

FILTER (

ALLSELECTED( 'CMJ PowerBI (2)'),

'CMJ PowerBI (2)'[Name] = _currentplayer

&& 'CMJ PowerBI (2)'[Date] <= _currentdate)

VAR _topNtable =

TOPN ( _topnnumber, _tableperplayer, CALCULATE ( MAX ( 'CMJ PowerBI'[Date] ) ), DESC )

VAR _result =

AVERAGEX ( _topNtable, [Eccentric duration measure:] )

RETURN

IF ( NOT ISBLANK ( [Eccentric duration measure:] ), _result )

Thanks again in advance

Sean