Forum Discussion
Rolling/Moving Average - Sports form guide
HI all, im looking to create a rolling average of the last 6 matches played by a team, so in the data below, what are the average points per game from the last 6, obviously this would be different per team. I can do this in tableau without much fuss, seems to be difficult in power BI, I can share the file if needed, i basically want to show this information on a line graph to help me spot trends
| Team Name | Date | Points |
| Aberdeen | 04/08/2019 | 3 |
| Aberdeen | 11/08/2019 | 0 |
| Aberdeen | 24/08/2019 | 1 |
| Aberdeen | 31/08/2019 | 3 |
| Aberdeen | 14/09/2019 | 1 |
| Aberdeen | 21/09/2019 | 3 |
| Aberdeen | 28/09/2019 | 0 |
| Aberdeen | 05/10/2019 | 1 |
| Aberdeen | 19/10/2019 | 3 |
| Aberdeen | 27/10/2019 | 0 |
| Aberdeen | 30/10/2019 | 3 |
| Aberdeen | 02/11/2019 | 3 |
| Aberdeen | 09/11/2019 | 3 |
| Aberdeen | 24/11/2019 | 1 |
| Aberdeen | 30/11/2019 | 3 |
| Aberdeen | 04/12/2019 | 1 |
| Aberdeen | 07/12/2019 | 0 |
| Aberdeen | 14/12/2019 | 3 |
| Aberdeen | 21/12/2019 | 0 |
| Aberdeen | 26/12/2019 | 3 |
| Aberdeen | 29/12/2019 | 1 |
| Accrington | 03/08/2019 | 0 |
| Accrington | 17/08/2019 | 1 |
| Accrington | 20/08/2019 | 0 |
| Accrington | 24/08/2019 | 0 |
| Accrington | 31/08/2019 | 3 |
| Accrington | 07/09/2019 | 1 |
| Accrington | 14/09/2019 | 0 |
| Accrington | 17/09/2019 | 1 |
| Accrington | 21/09/2019 | 1 |
| Accrington | 28/09/2019 | 3 |
| Accrington | 05/10/2019 | 1 |
| Accrington | 12/10/2019 | 0 |
| Accrington | 20/10/2019 | 3 |
| Accrington | 23/10/2019 | 0 |
| Accrington | 26/10/2019 | 0 |
| Accrington | 02/11/2019 | 1 |
| Accrington | 16/11/2019 | 0 |
| Accrington | 23/11/2019 | 3 |
| Accrington | 07/12/2019 | 1 |
| Accrington | 14/12/2019 | 3 |
| Accrington | 21/12/2019 | 1 |
| Accrington | 26/12/2019 | 3 |
| Accrington | 29/12/2019 | 3 |
Hi michaeldonnelly ,
try this.
Rolling AVG Points last 6 matches = CALCULATE ( AVERAGE ( 'Table'[Points] ), TOPN ( 6, FILTER ( ALLSELECTED ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ), 'Table'[Date], DESC ) )Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
6 Replies
- az38
Community Champion
you could create a measure like this if I undersyant correct "rolling average"
Measure = calculate(average(Table1[Points]);TOPN(6;filter(ALL(Table1);Table1[Date]<=SELECTEDVALUE(Table1[Date]) && Table1[Team Name]=selectedvalue('Table1'[Team Name]));Table1[Date];DESC))do not hesitate to give a kudo to useful posts and mark solutions as solution
- mwegener
Most Valuable Professional
Hi michaeldonnelly ,
try this.
Rolling AVG Points last 6 matches = CALCULATE ( AVERAGE ( 'Table'[Points] ); TOPN ( 6; FILTER ( ALL ( 'Table'[Date] ); 'Table'[Date] <= MAX ( 'Table'[Date] ) ); 'Table'[Date]; DESC ) )You may download my PBIX file from here.
Hope this helps.Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.On your file it seems to work perfectly, when I have copied the measure as the table names are all the same, I am getting a different result, which is baffling, I have attached my file
- mwegener
Hi michaeldonnelly ,
I don't have access to your link.
Please check the authorization.
It should be "Anyone with this link".
