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 

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

    Hi michaeldonnelly ,

     

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

    • michaeldonnelly's avatar
      michaeldonnelly
      Frequent Visitor
      6 years ago

      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'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 ( 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.