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 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
2 Replies
- Jihwan_Kim
Super User
Eccentric duration measure : =SUM ( Data[Eccentric Duration [ms]]] )Rolling avg of last five : =VAR _topnnumber = 5VAR _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 :] )RETURNIF ( 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 = 5VAR _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:] )RETURNIF ( NOT ISBLANK ( [Eccentric duration measure:] ), _result )Thanks again in advanceSean
