Days to first transition to multiple statuses

Hello!

I would like to get the number of days required for an issue to reach either status A, B or C (whatever happens first). I know about predefined measure ‘Transition to status first date’, but for my example I would need something like ‘Transition to statutes first date’.

I have the following code snippet but it only works for individual transition statuses:

  Percentile(
   Filter(
   Descendants([Issue].CurrentMember, [Issue].[Issue]),
   [Measures].[Transitions to status] > 0
   AND
   not isEmpty([Measures].[Issue resolution date])
   ),
   -- can substitute below function with DateDiffWorkdays if necessary
   DateDiffDays(
   [Issue].CurrentHierarchyMember.get('Created at'), 
   [Measures].[Transition to status first date]),
   90
  )

HI @Uros_Kovac
Welcome to the eazyBI community!

You are almost there with your calculation!
Measure “Transition to status first date”, used together with a calculated Transition Status member, always take the first transition date to any of the statuses included in the member.
So, if you select this Transition Status calculated member in the report Pages, you would get the first transition date to any of those statuses. Nothing should be added to the calculation (only Transition Statuses selected in Pages).

I would suggest changing the order of the filter conditions in the formula, so the filters by properties would be first; it improves the performance of the measure.

Percentile(
   Filter(
   Descendants([Issue].CurrentMember, [Issue].[Issue]),
   not isEmpty([Measures].[Issue resolution date])
   and 
   [Measures].[Transitions to status] > 0),
   -- can substitute the below function with DateDiffWorkdays if necessary
   DateDiffDays(
   [Issue].CurrentHierarchyMember.get('Created at'), 
   [Measures].[Transition to status first date]),
   90
  ) 

Best,
Ilze, support@eazybi.com

Hi Ilze!

Thank you for your reply and the performance tip. I’ve further simplified the query since last time a bit to:

Percentile(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Transitions to status] > 0
  ),
  -- can substitute the below function with DateDiffWorkdays if necessary
  DateDiffDays(
    [Issue].CurrentHierarchyMember.get('Created at'), 
    [Measures].[Transition to status first date]
  ),
  90
)

If I set time dimension in rows and set Month as hierarchy level member and filter by time between six months ago and today, I see that certain issues’ transitions are taken into an account multiple times.

Let me give you an example:

Lets say an issue was created on Jan 20 and was moved to status A on Jan 26. The above calculated measure would return 6 days for month January, which is ok. But if let’s say on Feb 2, the issue was moved to status B, then the above measure would take this issue into consideration yet again and would return 13 days. Of course, if transition to status B happened on Jan 28 (in the same month as transition to status A), the above formula would only return 6 days.

I would need a way where an issue would be considered only for the first time a transition to one of the desired statuses is made.

Is such a thing possible @ilze.leite?

@ilze.leite hate to bother you again, but would value your feedback.

Hi

Your observations are correct: if the issue had several transitions, the measure “Transition to status first date” would return the first transition date within each selected time period (a month in your case).

To take into account only the very first transition, you would add [Time].CurrentHierarchy.DefaultMember with the measure “Transition to status first date” to always take into account the very first transition to selected statuses.
Additionally, you need to add in the filter that this first transition happened in the selected time period (then issues would be counted only to the month when this first transition happened).

Percentile(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
 --filter issues that have the very first transition in the selected time period
    DateInPeriod(
     ([Measures].[Transition to status first date],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember)
  ),
  -- can substitute the below function with DateDiffWorkdays if necessary
  DateDiffDays(
    [Issue].CurrentHierarchyMember.get('Created at'), 
     ([Measures].[Transition to status first date],
      [Time].CurrentHierarchy.DefaultMember)
  ),
  90
)

Best,
Ilze