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 Metrics View'[Iteration Number]<SELECTEDVALUE('Agile Metrics View'[Iteration Number])+3&&'Agile Metrics View'[Iteration Number]>=SELECTEDVALUE('Agile Metrics View'[Iteration Number])))
Return RollingAvg



 

  • 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

4 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    "Usually rolling averages are calculated using the previous values, i.e. this sprint and the 2 immediately prior to it."

    That's what I trying to do sir

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      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
      • Anonymous's avatar
        Anonymous
        Not applicable

        Thanks!!