Sprint metrics based on planned end date, not sprint closure

Questions & Answers
, ,
1

I am trying to report on 3 (4?) sprint data points, but the problem is I don’t want to report on them based on when the Sprint was actually closed. I want to report on them based on the planned closed/end date.

I want to report on:

  • Issues created during the sprint
  • Issues closed during a sprint
  • Open issues
    • If current sprint: how many issues are open right now
    • If closed sprint: how many issues were open at the end of the sprint (planned end of sprint)

My tickets have multiple sprints listed, if they were not closed in the same sprint they were opened in.

Can anyone help me with these formulas?

2

Hello @kbarry
It looks like you also posted this question in Atlassian community. I’ll post the answer here as well in case there are other eazyBI users who are looking for something similar.
​
Assuming you have Sprints in your Rows, I recommend defining new calculated measures as follows:

​1. Issues created during the sprint. If you are looking for issues that literally have been created during the sprint, then this is the formula for that:

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateBetween([Measures].[Issue created date],
[Measures].[Sprint start date],
[Measures].[Sprint end date])
),
CASE
WHEN
[Measures].[Issues history]>=0
THEN
NonZero(([Measures].[Issues created],
[Sprint].CurrentHierarchy.DefaultMember))
END
)

However, if you are looking for all the issues that were committed and issues that got added to the sprint during the Sprint, then you might want to use the following formula.
​

[Measures].[Sprint issues committed] +
[Measures].[Sprint issues added]

​
2. ​Issues closed during a (planned) sprint :
​

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBetween([Measures].[Issue resolution date],
    [Measures].[Sprint start date],
    [Measures].[Sprint end date])
  ),
  CASE
  WHEN
  [Measures].[Issues history]>=0
  THEN
  NonZero(([Measures].[Issues resolved],
  [Sprint].CurrentHierarchy.DefaultMember))
  END
)

3. ​Open issues in current sprint or issues that were open after sprint planned end date.

CASE 
WHEN
[Sprint].CurrentHierarchyMember.Get('Status') = "Closed"
THEN
Sum(
  Filter(
    DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
    DateCompare([Measures].[Issue created date],
    [Measures].[Sprint end date])<=0 AND
    (DateCompare([Measures].[Issue resolution date],
    [Measures].[Sprint end date])>0 OR
    IsEmpty([Measures].[Issue resolution date]))
    
  ),
  CASE
  WHEN
  [Measures].[Issues history]>=0
  THEN
  NonZero(
  ([Measures].[Open issues],
  [Time].[Day].DateMember([Measures].[Sprint end date]),
  [Sprint].CurrentHierarchy.DefaultMember))
  END
)
ELSE
[Measures].[Open issues]
END

Best wishes,

Elita from support@eazybi.com