Unification of Sprint Names

Hello All,
We have multiple teams working on one product. Each team will have have a Sprint with specific format:

Example: 2019 > S1 > UM > Team 1, 2019 > S1 > UI > Team 2, 2019 > S1 > ABC > Team 3
We need to generate Sprint Velocity Report for Sprint 1 (S1) as in the above example. Can you help us on how to create new Calculated Field that show only the Sprint Names as 2019 > S1

Hi, hope this helps.

Check the exact name inside: MATCHES ".*2019 > S1.*" , as you might have spaces, or not.

Aggregate(
Order(
Filter(
[Sprint].[Sprint].Members,
[Sprint].CurrentHierarchyMember.Name MATCHES ".*2019 > S1.*"
),
[Sprint].CurrentHierarchyMember.Name, BASC
)
)

1 Like

Thanks for the response. This works for some extent but does not solve my issue still. What I am looking for is to generate Sprint Velocity chart. To do that the Sprint names needs to be changed to
2019 > S1 -----------Aggregated so SP from all teams
2019 > S2-----------Aggregated so SP from all teams
2019 > S3-----------Aggregated so SP from all teams

@nagendrabr,

the proposed solution by @arama_mihai is on the right track.
It is possible to adjust the Velocity calculation to base it on sprints matching the name pattern.

If we had the member in the Sprint dimension as proposed by @arama_mihai (say, “Closed S1 sprints”, perhaps with the additional condition to filter closed sprints only) the formula for velocity would be:

  AVG(
    Tail(
      -- filter last 5 closed S1 sprints starting from current sprint
      Filter(
        Head(
          Cache(ChildrenSet([Sprint].[Closed S1 sprints])),
          Rank([Sprint].CurrentMember,
            Cache(ChildrenSet([Sprint].[Closed S1 sprints]))
          )
        ),
        -- only sprints with committed story points are retrieved
        [Measures].[Sprint Story Points committed] > 0
      ), 5
    ),
    [Measures].[Sprint Story Points completed]
  )

Note also that this formula expects that you have added the “Closed S1 sprints” to the report rows and expand to the sprint level.

Kindly,
Janis, eazyBI support