Measuring net time of resolution

Questions & Answers
1

Hi,

I’m struggling to create a report that unlike the sample reports for cycle/lead time of issues, excludes the time spent in passive statuses, before the item satisfies the ready for development status.
We have a discovery and shaping process, where a ticket starts as new and transitions through drafting and shaping phases till we declare it is ready for development. At that moment it becomes a proper development item.
In concrete terms, for bugs we have the following preparatory statuses:
[BUG] NEW BUG,
[BUG] In Triage,
[BUG] DRAFTING
They all fall under the “To Do” status category

[BUG] Awaiting DEV is the first active state and it’s still in the To Do category

There are also other active states which belong to the In Progress category:
[BUG] IN DEV
[BUG] AWAITING PR
[BUG] AWAITING RELEASE TO FT
[BUG] AWAITING FT
[BUG] IN FEATURE TESTING
[BUG] BLOCKED IN DEV
[BUG] BLOCKED IN FT

and finally we have the Done statuses:
[BUG] Closed and [BUG] Done

What I need to measure is the sum of the times in the statuses which we consider active.
Unfortunately, it can’t be a simple diff between the status Bug Closed/Done and BUG awaiting dev, because sometimes developers migrate the bug straight in the BUG In dev status, skipping the BUG awaiting dev, or a bug is closed as rejected in triage without ever spending a moment in the active states.

So, the report that I need should show months on the X-Axis and Count of issues resolved and average net resolution time on Y-axis. I will limit the report with Priority filter so I can select P1s, P2s or both.
I tried this calculated measure that always returns zero records as soon as I add the issue name to be bug:
Sum(
Filter(
DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues resolved] > 0
AND [Issue Type].CurrentMember IS [Issue Type].[Bug]
),
[Measures].[Total resolution days] -
Sum(
{[Transition Status].[[[BUG]] NEW BUG],
[Transition Status].[[BUG]] In Triage],
[Transition Status].[[BUG]] DRAFTING]},
[Measures].[Days in transition status]
)
)
I also tried aggregating transition statuses in one “Active” but the results never matched accurately the times that I calculated by hand.

Any ideas how to accomplish?

P.S. I would rather not show issues that transition from any of the preparatory statuses to the final statuses.

2

P.S. P.S. I would rather not show issues that transition directly from any of the preparatory statuses to the final statuses.

3

Hi @Davor_Spasoski,

Thanks for posting your question. The general direction is right, but we made some corrections to your code as shown & explained below:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentMember, [Issue].[Issue]),
    --[Measures].[Issues resolved] > 0
    --AND [Issue Type].CurrentMember IS [Issue Type].[Bug]
    --more efficient way to filter by property first
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember) AND
    [Measures].[Issue type] = "Bug"

  ),
  CASE WHEN
    --issue went through In Progress category before Done
    ([Measures].[Transitions from status],
    [Transition Status.Category].[In Progress]) > 0
  THEN
    [Measures].[Total resolution days] -
    Sum(
      {[Transition Status].[[[BUG]] NEW BUG],
      [Transition Status].[[BUG]] In Triage],
      [Transition Status].[[BUG]] DRAFTING]},
      [Measures].[Days in transition status]
    )
  END
)

I hope this helps!

Best,
Marita // support@eazybi.com

4

Hi @Marita_Norvele,

Thank you very much for your reply!
Some nice tricks to improve speed in your example.
To be honest, this report gave me a lot of trouble because I’m not proficient in writing custom measures in the EazyBI syntax.
I had to resolve the problem (and I think I did) much earlier than your reply came and had to combine some sample reports combined with your AI assistant, but it was a long road with many iterations. Although I much prefer to subtract the time spent in the New/Triage/Drafting from the total resolution days, just like in your example, for some reason it’s not giving me accurate results.
So I had to sum-up all the “active” times. I somehow suspect that this part below is not catching the statuses well:

Sum(
      {[Transition Status].[[[BUG]] NEW BUG],
      [Transition Status].[[BUG]] In Triage],
      [Transition Status].[[BUG]] DRAFTING]},
      [Measures].[Days in transition status]
    )

On the other hand, this works, though I don’t like it as a model to expand it for other types of issues as it will have three dozens of statuses and I bet it will be very slow to execute:

IIF(
  [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
  -- First part: calculation when no specific time period is selected
  Sum(
    {[Transition Status].[[BUG]] Awaiting Dev],
     [Transition Status].[[BUG]] In Dev],
     [Transition Status].[[BUG]] Awaiting PR],
     [Transition Status].[[BUG]] Awaiting Release To FT],
     [Transition Status].[[BUG]] Awaiting FT],
     [Transition Status].[[BUG]] In Feature Testing],
     [Transition Status].[[BUG]] BLOCKED IN DEV],
     [Transition Status].[[BUG]] BLOCKED IN FT],
     [Transition Status].[[DEFECT]] Awaiting Dev],
     [Transition Status].[[DEFECT]] In DEV],
     [Transition Status].[[DEFECT]] Awaiting PR],
     [Transition Status].[[DEFECT]] Awaiting Release to FT],
     [Transition Status].[[DEFECT]] Awaiting FT],
     [Transition Status].[[DEFECT]] In Feature Testing]
    },
    ([Measures].[Days in transition status],
     [Status.Category].[Done])
  ) / [Measures].[Issues resolved],
  
  -- Modified second part for specific time periods
  AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
      )
    ),
    CASE WHEN [Measures].[Issues resolved] > 0
    THEN
      CoalesceEmpty(
        Sum(
          {[Transition Status].[[BUG]] Awaiting Dev],
           [Transition Status].[[BUG]] In Dev],
           [Transition Status].[[BUG]] Awaiting PR],
           [Transition Status].[[BUG]] Awaiting Release To FT],
           [Transition Status].[[BUG]] Awaiting FT],
           [Transition Status].[[BUG]] In Feature Testing],
           [Transition Status].[[BUG]] BLOCKED IN DEV],
           [Transition Status].[[BUG]] BLOCKED IN FT],
           [Transition Status].[[DEFECT]] Awaiting Dev],
           [Transition Status].[[DEFECT]] In DEV],
           [Transition Status].[[DEFECT]] Awaiting PR],
           [Transition Status].[[DEFECT]] Awaiting Release to FT],
           [Transition Status].[[DEFECT]] Awaiting FT],
           [Transition Status].[[DEFECT]] In Feature Testing]
          },
          ([Measures].[Days in transition status],
           [Status.Category].[Done],
           [Time].CurrentHierarchy.DefaultMember)
        ),
        0
      )
    ELSE
      NULL
    END
  )
)

You can ignore the defect statuses as I further reduce it to bugs only using a page filter for issue type before I compare the results.

For example, this ticket DYN-3117 has the following history:

Created In triage Awaiting Dev Done Passive Active Total
DYN-3117 11/04/2024 10:39 18/04/2024 14:21 15/05/2024 17:05 05/11/2024 15:34 34.26805556 173.9368056 208.2048611

With the summation approach I correctly get the ~174 days in active statuses, but with the subtraction (as in your example) I get a number that equals the total resolution days.

On different examples I get different times, depending on the statuses the ticket was in.

1 Like