How to count closed Sprints within time perion

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.

Thank you for your advise in advance!

Hi!

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]
    ))
  )
)))

Lauma / support@eazybi.com