Forum Discussion
Measure Moving Average last 5 values
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 calcul...
Jihwan_Kim
Super User
Super User
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 )
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