Forum Discussion

michaeldonnelly's avatar
michaeldonnelly
Frequent Visitor
6 years ago
Solved

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 pe...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

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's avatar
michaeldonnelly
Frequent Visitor
6 years ago

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