Counting Distinct Issues Transitioned Over Sprint Period

Questions & Answers
1

I am looking for a way to count the number of distinct issues whose status last transitioned over the period of a Sprint.
An issue may transition multiple times, but I want to count distinct issues whose last transition was within the sprint.
An issue won’t be tagged with the sprint, so need a way to use the sprint as a time frame.

I want the Sprint to be selectable so have added as a Page, and then have Labels dimension in Rows, so the count is divided by label…

I can get a sum of all the transitions like this…

Sum(
  Filter(
    [Time].[Day].Members,
    [Measures].[Time within sprint] > 0
  ),
  (
    [Measures].[Transitions to status issues count],
    [Sprint].CurrentHierarchy.DefaultMember
  )
)

But this counts the transitions rather than the unique issues that made those transitions, so if an issue made two transitions in the time period it would count twice…

I believe I need to Filter on the Issues dimension (rather than time) and maybe compare if the Transition to status last date is between Sprint start date and Sprint end date, but I can’t work out the correct formula…

Any pointers in the right direction would be great!
Thanks

2

Hi @Mark_Wiggins,

You are on the right track - you should use the Transition to status last date with DateBetween(…) function. Here is a formula that would go through issues and count the ones that have the last transition date (even if the issue is not within the Sprint) between sprint dates:

NonZero(Count(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateBetween(
    (
      [Measures].[Transition to status last date],
      [Sprint].DefaultMember
    ),
    [Measures].[Sprint start date],
    [Measures].[Sprint end date]
  )
)))

Lauma / support@eazybi.com

3

Thanks @lauma.cirule
I had seen the Descendents formula around but couldn’t work out how to implement it to get what I wanted, so thank you!

1 Like