Aggregate Sprint Names

Questions & Answers
1

Hi,

I have several Sprint on different projects and I want to show them together on my report.
Example:
Project1 has ‘Sprint 1 P1’, Project2 has ‘Sprint 1 P2’ and so on… I want to show all the issues on Sprint 1 no matter what project it is.
I was thinking on some like:
Aggregate({
CASE WHEN [Measures].[Issue Sprint] MATCHES ‘Sprint 1*’
THEN
‘Sprint One’
END
})
But I have the error 'No Function matches signature ‘{}’

Any clue?

2

Hi @Daniel_Luevano,

You can create a calculated member in the Sprint dimension using aggregate function and regular expression.
Formula to use would be this:

Aggregate(
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentMember.Name MATCHES ".*Sprint 1.*"
  )
)

You can also check our documentation for examples of other regular expressions if you need to make a regular expression that checks if this word is only at the beginning of the Sprint.

1 Like
3

Is it possible to make this dynamic based on the last 6 characters of the sprint name? We’re running parallel sprints for one of our dev teams. I need to aggregate hours spent for the last five sprints to determine the whole team’s velocity.

4

Hi @Shel_Price ,
Can you share an example?
One of the options would be to check if the sprint ends with your required expression, for example, this formula

Aggregate(
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentMember.Name MATCHES ".*Sprint 1"
  )
)

would check if the Sprint members ends with “Sprint 1”.
If this is not what you mean, can you share an example on how you would like to aggregate sprints based on last 6 characters?

Kind regards,
Gerda//support@eazyBI.com

5

I want to aggregate [Sprint].[Sprint].[PMWeb-G.2021.13] and [Sprint].[Sprint].[PMWeb-R.2021.13] as PMWeb2021.13, then [Sprint].[Sprint].[PMWeb-G.2021.14] and [Sprint].[Sprint].[PMWeb-R.2021.14] as PMWeb2021.14 and so on, preferably not by manually creating separate measures for each set. When displayed, the data should show by the grouped sprints .13, .14 and so on. Then I need to be able to sum the hours spent during the first 2 weeks of our 3 week sprints.

6

Hi @Shel_Price ,

For my previous calculation to work for your naming pattern, this would be the formula for “Sprint 13” (the same should be repeated for the next sprints):

Aggregate(
  Filter(
    [Sprint].[Sprint].Members,
    [Sprint].CurrentMember.Name MATCHES ".*2021.13$"
  )
)

Or you can try this regular expression to improve the aggregated result: “^PMWeb.*2021.13$”

But if you want for them to be created automatically then you may find this community answer more suitable:

For that solution to work for your use case, you need to adjust the JavaScript in REST API import option to match your Sprint name pattern.

Kind regards,
Gerda