Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling Average - Needs to be validated please

Is this calculation look right? Rolling Average = Var NumSprints = 3 Var RollingAvg = Calculate (AVERAGE('Agile Metrics View'[Story Points]),FILTER(ALLSELECTED('Agile Metrics View'),'Agile Me...
  • johnt75's avatar
    4 years ago

    For each sprint this will work out the average over this sprint and the subsequent 2 sprints, is this what you are trying to do? Usually rolling averages are calculated using the previous values, i.e. this sprint and the 2 immediately prior to it.

    Also, this will break if any sprints are filtered out e.g. by slicers, given that it is dependent on the iteration number increasing by 1 each time.

  • johnt75's avatar
    johnt75
    4 years ago
    Rolling Average =
    VAR NumSprints = 3
    VAR RollingAvg =
        CALCULATE (
            AVERAGE ( 'Agile Metrics View'[Story Points] ),
            FILTER (
                ALLSELECTED ( 'Agile Metrics View' ),
                'Agile Metrics View'[Iteration Number]
                    <= SELECTEDVALUE ( 'Agile Metrics View'[Iteration Number] )
                    && 'Agile Metrics View'[Iteration Number]
                        > SELECTEDVALUE ( 'Agile Metrics View'[Iteration Number] ) - NumSprints
            )
        )
    RETURN
        RollingAvg