Rolling/Moving Average - Sports form guide

[michaeldonnelly]

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

[mwegener]

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.

[michaeldonnelly]

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

Link 

[mwegener]

Hi @michaeldonnelly ,

I don't have access to your link.
Please check the authorization.
It should be "Anyone with this link".

[michaeldonnelly]

Doesnt seem to want to allow me the option to share without and email from my one drive, so uploaded to dropbox, link below

Try this one 

[mwegener · Accepted solution]

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.

[az38]

hi @michaeldonnelly 

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