I need to count closed Sprints in all imported projects within time period in one particular project from Epic creation date till current Epic change status to particular one.
For example: Project1 EpicX created 01.01.2010 transition to status “Delivered” 10.01.2011. In Project2 during this period was closed 5 Sprints, in Project3 7 Sprints etc.
In Jira and eazyBI Sprints are organized in Boards not Projects. I suggest using the Board level from Sprint dimension to count Sprints closed between two dates.
In the following formula
I have hardcoded the Epic to get the created and transition to Delivered dates. Please make sure the Project and Epic issue name matches the name from Issue dimension, including key.
To determine if sprint is closed, we look not only if the Sprint is completed and has a completion date, but also if there are completed issues in the sprint.
Here’s the formula example:
NonZero(Count(Filter(
Descendants([Sprint].CurrentMember, [Sprint].[Sprint]),
[Measures].[Sprint closed?] = "Yes" AND
[Measures].[Sprint issues completed] > 0 AND
DateBetween(
[Measures].[Sprint end date],
DateParse((
[Issue].[Project1].[EpicX],
[Measures].[Issue created date]
)),
DefaultContext((
[Issue].[Project1].[PR-1 EpicX],
[Measures].[Transition to status last date],
[Transition Status].[Delivered]
))
)
)))