Issues transitioned to a particular status X days before the end of a sprint

Questions & Answers
1

I am trying to put together a report that shows the percentage of parent issues (ie not including sub-tasks) of a particular type (spikes and stories in my case) that were transitioned to a particular custom status (“Ready for QA” in my case) at least 3 days before the end of a sprint. So, if FOO-1 was transitioned to “Ready for QA” 4 days before the end of a sprint, but FOO-2 was transitioned only 1 day before the end of the sprint, the calculated measure would indicate “50%”.

2

Hi Dave,

You may want to create three measures:

  1. getting all issues transited to Ready for QA 4 or more days before the sprint end (completion) date
  2. getting all issues transited Ready for QA during the sprint
  3. the final calculation, getting the % between those two measures above.
    While you can create only the final calculation that includes both formulas, it is easier to test them one by one.

1
Below is the calculation for a measure that iterates through all issues, looking for those in this sprint, with a particular issue type (story in my case, as you need parent-type issues only) and having been transited to the status 4 or more days before sprint end or completion date:

CASE WHEN
[Sprint].CurrentMember.Level.name = "Sprint"
THEN
  NonZero(SUM(
Filter(
  Descendants([Issue].Currentmember, [Issue].[Issue]),
-- by sprint property, issues having been in this sprint and only stories
   CoalesceEmpty([Issue].CurrentMember.Get("Sprint IDs"),
     CoalesceEmpty([Measures].[Issue Sprint], "")
   ) MATCHES ".*"|| Cast([Sprint].CurrentMember.Key as string) ||".*"
  AND [Measures].[Issue type] = "Story"),
  CASE WHEN
   DateDiffDays(
     ([Measures].[Transition to status last date],
      [Transition Status].[Ready for QA]),
      CoalesceEmpty([Sprint].CurrentHierarchyMember.get('Complete date'), 
                    [Sprint].CurrentHierarchyMember.get('End date'))
   )>=4
   THEN
--count issues having been transited to this status
Val(([Measures].[Transitions to status issues count],
 [Transition Status].[Done]))
  END
  ))
END

2
To get all issues transitioned to this status during the selected sprint, create another measure where use a tuple alone:

([Measures].[Transitions to status issues count],
 [Transition Status].[Ready for QA])

Check issue type and status names in the formulas! Set integer formatting for both measures.

3
When you have tested the first two are correct, create the % calculation: divide first measure by the second measure. Set percentage formatting for the measure.

Best,
Ilze, support@eazybi.com