Forum Discussion
Rolling Average - Needs to be validated please
Is this calculation look right?
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.
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
- johnt75
Super User
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.
- AnonymousNot 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
Super 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- AnonymousNot applicable
Thanks!!