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

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%”.

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.

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:

[Sprint] = "Sprint"
  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"),
     ([Measures].[Transition to status last date],
      [Transition Status].[Ready for QA]),
      CoalesceEmpty([Sprint].CurrentHierarchyMember.get('Complete date'), 
                    [Sprint].CurrentHierarchyMember.get('End date'))
--count issues having been transited to this status
Val(([Measures].[Transitions to status issues count],
 [Transition Status].[Done]))

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.

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.