EazyBI: calculated values based on sprint

[Kim Barry]

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

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?

[elita_kalane]

Hi @Kim Barry  
​Thanks for raising the question! 
​
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

I hope the above is what you were looking for! 

Elita from support@eazybi.com
​