How to get Velocity Calculated across Multiple Teams

Questions & Answers
1

I’m looking for a method to get a report showing Velocity across a number of Sprints (4) that includes multiple projects/teams. In my case each team is using a separate project. As close as I’ve gotten thus far includes;

Although all teams are on the same sprint cadence, teams are all in different projects and thus different Sprints in Jira so we use a common naming convention that lets me identify the collective “sprint” through a calculated Member

Aggregate(

Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentMember.Name MATCHES “.FY21.PI2.S1.”
)
)

Teams are further broken down into Divisions (large organization) I need to identify a subset of teams rather than using all teams within our Jira instance which I have another calculated member for

Aggregate(
{
[Project].[Project 1],
[Project].[Project 2],
[Project].[Project 3],
[Project].[Project 4]
}

)

When put together my resulting table looks something like this, but the combined Velocity for all teams in that Sprint doesn’t calculate. How can I get this included?
image

2

Essentially I’m looking for a method to calculate total Total Combined Velocity Over the Last 4 Sprints for a set of teams (Projects 1-4) using the sprint/s that include “FY21.PI2.S1” in the Sprint name…

3

Hi @Roy_Stiles ,

Please share the formula for calculating the “Velocity Over Last 4 Sprints”. I suspect there could be the key to your question.

Alternatively, you can export and share the definition of the report to our support email - Create reports - eazyBI.

Best,
Roberts // support@eazybi.com

4

Formula being used to calculate Velocity Over Last 4 Sprints (Now calculating across 6 Sprints) is as follows;

CASE
WHEN
  [Sprint].CurrentMember is [Sprint].DefaultMember
  OR
  -- for closed sprints only
  [Sprint].CurrentMember.getBoolean('Closed') AND
  NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
  [Measures].[Sprint Story Points committed] > 0
THEN
  AVG(
    Tail(
      -- filter last 6 closed sprints starting from current sprint
      Filter(
        Head(
          Cache(ChildrenSet([Sprint].[All closed sprints])),
          Rank([Sprint].CurrentMember,
            Cache(ChildrenSet([Sprint].[All closed sprints]))
          )
        ),
        -- only sprints with committed story points are retrieved
        [Measures].[Sprint Story Points committed] > 0
      ), 6
    ),
    [Measures].[Sprint Story Points completed]
  )
END
5

Hi @Roy_Stiles ,

The velocity formula is designed to work with Sprint dimension “Sprint” level members. It won’t show results for a Sprint dimension calculated member. The formula compares and ranks the Sprint dimension current member to all closed Sprints. From there, it sees the velocity of the last six closed Sprints, including the one in rows.

For a Sprint dimension calculated member, I recommend calculating the average of the Story points completed in the four Sprints instead. See the formula below:

CASE
WHEN
  [Sprint].CurrentMember is [Sprint].DefaultMember
  OR
  -- for closed sprints only
  [Sprint].CurrentMember.getBoolean('Closed') AND
  NOT IsEmpty([Sprint].CurrentMember.get('Complete date')) AND
  [Measures].[Sprint Story Points committed] > 0
THEN
  AVG(
    Tail(
      -- filter last 6 closed sprints starting from current sprint
      Filter(
        Head(
          Cache(ChildrenSet([Sprint].[All closed sprints])),
          Rank([Sprint].CurrentMember,
            Cache(ChildrenSet([Sprint].[All closed sprints]))
          )
        ),
        -- only sprints with committed story points are retrieved
        [Measures].[Sprint Story Points committed] > 0
      ), 6
    ),
    [Measures].[Sprint Story Points completed]
  )
  -- average for Sprint calculated member
WHEN [Sprint].CurrentMember.Level.Name = '(All)'
THEN 
  Avg(
    Filter(
      ChildrenSet([Sprint].CurrentMember),
      [Measures].[Sprint closed?] = 'Yes'
      AND
      [Measures].[Sprint Story Points committed] > 0
    ),
    [Measures].[Sprint Story Points completed]
  )
END

Best,
Roberts // support@eazybi.com