Velocity Report

Questions & Answers
1

Trying to create a sprint velocity report and I have the Story Point committed and Story point resolved but looking for how in the example the running velocity is calculated and can it be dynamic as to the number of sprints

We are reacting a report the has Story Points committed, Story Points Resolved but are splinting the data by a custom field called teams that span multiple projects.

So when you select a team you may have 4 sprints but another team has more projects and more sprints so want too be able to calculate story points resolved / total sprints closed

2

Hi Brian,

The Running velocity uses Tail(…) function to get last five closed sprints that have story points committed in current report context. For each team, this would be a different set of sprints matching the Teams that are linked to the stories. You can check what sprints are returned for each row by adding the following formula in new test calculated member (note that you should view this in Table view).

settostr(
    Tail(
      -- filter last 5 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
      ), 5
    )
)

If you wish to have Running velocity not only for last five sprints but each Sprint add all previous, you should remove the Tail(…) function from the Running velocity formula 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(
      -- filter last 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
    ),
    [Measures].[Sprint Story Points completed]
  )
END

Please let me know if you have further questions regarding this!
Kind regards,
Lauma / support@eazybi.com