Count Issues closed on last sprint day

Questions & Answers
,
1

Hello,
I’m trying to set up a measure counting how many issues have been closed in the last sprint day.
This is what I came up to:

CASE WHEN
[Sprint].Currentmember.Level.name = “Sprint”
THEN
Count(
Filter(
Descendants([Issue].Currentmember, [Issue].[Issue]),
[Sprint].CurrentMember.Name = [Measures].[Issue Sprint]
AND
Datediffdays(
DateWithoutTime([Issue].CurrentHierarchyMember.get(‘Closed at’)),
DatewithoutTime([Sprint].CurrentMember.get(‘End date’))) = 0
)
)
END

May it be correct?

2

Hi @Mauro_Bennici,

This solution looks almost good to go, but I would suggest adding a measure into your calculation [Measures].[Sprint issues completed]>0.
The improved formula would look like this:

CASE WHEN
  [Sprint].Currentmember.Level.name = "Sprint"
THEN
  NonZero(
    Count(
      Filter(
        Descendants([Issue].Currentmember, [Issue].[Issue]),
        [Sprint].CurrentMember.Name = [Measures].[Issue Sprint]
        AND
        [Measures].[Sprint issues completed]>0
        AND
        Datediffdays(
        DateWithoutTime([Issue].CurrentHierarchyMember.get('Closed at')),
        DatewithoutTime([Sprint].CurrentMember.get('End date'))) = 0
      )
    )
  )
END

You can test your calculation to check if it returns the correct data.
Another thing to play around is to check if it is better for you to use Issue property Closed at or change it to Resolved at when resolution date was set, same goes to Sprint property End date or changing it to Complete date when Sprint actually was completed.

best,
Gerda // support@eazyBI.com

3

Thanks very much for your help!

1 Like
5

@gerda.grantina I tweaked a little with the measures, and discovered that the “closed at” issue property is overwritten for every transition in the same category (yeah, I have more than one status in the “complete” category sigh).
How can I track down the first transition date to the “complete” status category?
regards.

6

I might have come to a solution:

CASE WHEN
  [Sprint].Currentmember.Level.name = 'Sprint'
THEN
   --count the issues that came to a closing status for the first time on sprint closing day
    Count(
      -- filter issues for present sprint and roughly cut subtasks and null issues
      Filter(
        Descendants([Issue].Currentmember, [Issue].[Issue]),
        [Sprint].CurrentMember.Name = [Measures].[Issue Sprint]
        AND
     -- the sprint should have at leat a completed issue
        [Measures].[Sprint issues completed]>0
        AND
        [Measures].[Issue Story Points] > 0
        AND
        Datediffdays(
        DateWithoutTime(
   --check transition dates, pick first date in a completed category status
        ([Measures].[Transition to status first date], 
          [Transition Status.Category].[Completato])
          ),
        --DateWithoutTime([Issue].CurrentHierarchyMember.get('Closed at')),
        --DatewithoutTime(( [Measures].[Sprint actual end date], [Sprint].CurrentMember))
        DatewithoutTime([Sprint].CurrentMember.get('End date'))) = 0
      )
    )
END