cancel
Showing results for
Search instead for
Did you mean:
See all
Top groups
Explore all groups

EazyBI: calculated values based on sprint

Kim Barry February 23, 2023

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?

1 answer

0 votes
elita_kalane
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 27, 2023

​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

Suggest an answer

Log in or Sign up to answer