Getting the last completed sprint by name, depending on page selection

Questions & Answers
1

Background - we have several squads that each maintain their own sprints with a related sprint naming scheme that is specific to each squad. For example, Squad 1 always calls their sprints “A-Team Sprint #” and Squad 2 always calls their sprints “B-Team Sprint #”.

When we create velocity reports, we want it to show the last 6 closed sprints for a specific squad. We have Sprint as the row and the Story Points Completed measure as the column.

We needed a solution for finding the last (and second-to-last, etc) completed sprint for a specific squad, so we used our custom field “Squad” as a dimension and created a Measure called “Sprint Name” to map Squad values to their associated names. Squad is used as a Page member and the value is present on the issues that need to be counted.

Here is the Sprint Name measure:

CASE [Squad].CurrentMember.Name
WHEN 'Squad 1'
THEN 'A-Team'
WHEN 'Squad 2'
THEN 'B-Team'
WHEN 'Squad 3'
THEN 'C-Team'
END

Then we created a calculated member in the Sprint dimension that filters based on that name:

 Tail(Order(
    Filter([Sprint].[Sprint].Members,
      [Sprint].CurrentMember.GetBoolean('Closed') AND
      [Sprint].CurrentMember.Name MATCHES "[Measures].[Sprint Name] || ".*"),
    [Sprint].CurrentMember.get('Activated date'),BASC),
    1).Item(0)

This works fine when one Squad is selected in the page dropdown, but now we have a case where we need to show a combined velocity chart (two or more squads).

I was able to hard-code a calculated member to show me two squads like this:

Aggregate({
-- Squad 1
  Tail(Order(
    Filter([Sprint].[Sprint].Members,
      [Sprint].CurrentMember.GetBoolean('Closed') AND
      [Sprint].CurrentMember.Name MATCHES "A-Team Sprint.*"),
    [Sprint].CurrentMember.get('Activated date'),BASC),
    1).Item(0),
-- Squad 2
  Tail(Order(
    Filter([Sprint].[Sprint].Members,
      [Sprint].CurrentMember.GetBoolean('Closed') AND
      [Sprint].CurrentMember.Name MATCHES "B-Team Sprint.*"),
    [Sprint].CurrentMember.get('Activated date'),BASC),
    1).Item(0)
})

But I need this to be more dynamic. How can I use the page selection to dictate which sprints are included in the aggregate?

2

Hello @cchase ,
I suggest using additional data mapping and then creating a Sprint custom hierarchy based on your Sprint name pattern. See the example here:

After that, you can use formulas from this report to create a sprint velocity measure: Sprints by sprint name velocity - Issues - eazyBI Demo Training - eazyBI

Kindly,
Gerda // support@eazybi.com