Calculating rolling average velocity for each sprint

Questions & Answers
, , , ,
1

Hello Team ,

I have a requirement wherein I want to publish the rolling average velocity for each sprint that is part of my calculated member " last 6 sprints ".

For ex : Sprint 1 to Sprint 6 are my last 6 sprints. Then the report should look like

Avg vel. for Sprint 1 = story points completed in sprint 1

Avg vel for Sprint 2 = (sum of story points completed ( Sprint 1 + Sprint 2 )) / 2

And so on …

2

Hi,

A standard measure gives the average velocity for the last 5 sprints (Running Story Points velocity for 5 closed sprints). You may check that the same formula can be reused, replacing the 5 with 6.

If you have a completely customized Sprint member, the following universal solution can be applied:

Sum(
  Head(
    ChildrenSet([Sprint].[Last 6 closed sprints]),
    Rank([Sprint].CurrentHierarchyMember,
     ChildrenSet([Sprint].[Last 6 closed sprints]))
  ),
  [Measures].[Sprint Story Points completed]
)/
Rank([Sprint].CurrentHierarchyMember,
     ChildrenSet([Sprint].[Last 6 closed sprints]))

The report could look like this:

image
image1344×796 62.9 KB

Kindly,
Janis, eazyBI support

3

Hi @janis.plume ,

How is ‘Last 6 closed sprints’ calculated?

4

Hi,

The following formula is behind the last 6 closed sprints:

Aggregate(
  Head (
  Order(
    Filter([Sprint].[Sprint].Members,
      [Sprint].CurrentHierarchyMember.getBoolean('Closed') AND
      NOT IsEmpty([Sprint].CurrentHierarchyMember.get('Complete date'))),
    [Sprint].CurrentHierarchyMember.get('Start date'),
    BDESC
  ),
  6)
)

Kindly,
Janis, eazyBI support