Lead time for issue types from status C to G

[Dilan]

Hi All

Im new to eazybi and im struggling to build a report that shows the lead time it took for an issue type ( story, defects etc) at status g to get there in days or working days from status c

Workflow
Status a - to do
Status b - backlog
Status c- in refinement
Status d- ready for dev
Status e- ready for test
Status f- testing
Status g- QA complete

Please can i get assistance with my query?

[gerda.grantina]

Hi @Dilan ,
You can check out example reports in eazyBI demo dashboard Age Lead, and Cycle Time. It represents different cases.
You can read more about it here in this answer provided by Daina:

Calculate Lead/Cycle Time From Specific Statuses

You would like to check out our example reports in our demo accounts for Cycle and Lead times. We set a dashboard Age Lead, and Cycle Time there to represent different cases. Here are some measures I would like to point as examples: Cycle time for a specific set of statuses for resolved issues and measure Rolling average cycle time in particular. This measure contains two approaches. The formula by default works for any statuses in status category In Progress. There is an alternative solution …

Also, check this answer from Martins on how to calculate average time based on status transition:

Avg days between two statuses

@joejames Try this code: CASE WHEN ( [Measures].[Transitions to status], [Transition Status].[Waiting for Production] ) > 0 THEN AVG( Filter( Descendants([Issue].CurrentMember, [Issue].[Issue]), ( [Measures].[Transitions to status], [Transition Status].[Waiting for Production] ) > 0 ), DateDiffDays( ( [Measures].[Transition to status first date], [Transition Status].[In Progress], [Time].CurrentHierarchy.DefaultMember…

best,
Gerda // support@eazyBI.com

[Dilan]

Hi Gerda, None of those seem to be working for me i get either a memory error or no data is shown.

CASE WHEN ([Transition Status].[Done],[Measures].[Transitions to status]) > 0
THEN
[Measures].[Workdays in transition status] / [Measures].[Transitions from status issues count]
END

This is the Measure i have used which is the closest im getting to what i want… however im finding that its not calculating number of work days from In progress to Done.

In Pages i filter by Done Status, Issue Type i filter to Feature
In Rows i have created a aggregate of all the Transitions from Status C to Status G

Hopefully you can help me to refine this futher?

[Dilan]

Hi @gerda.grantina - it looks like the below report measure query works, however 2 issues:

1. im unable to get a average workdays view
2. im unable to add time in Rows so i can see a trend over time

Any advice?

– days in transition status when issue was in this status in previous times
IIF(
– if report uses Status dimension instead of Transition status it should work as well:
[Status].CurrentHierarchyMember.Level.Name = “Status” and Not [Transition Status].CurrentHierarchyMember.Level.name = “Transition status”,
([Measures].[Days in transition status],
[Transition Status].[Transition status].GetMemberByKey(
[Status].CurrentHierarchyMember.Key
)),
[Measures].[Days in transition status])
+
– days since last transition to this status
NonZero(SUM(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– for unresovled issues only
IsEmpty([Issue].CurrentHierarchyMember.Get(“Resolved at”))
AND
IIF([Transition status].CurrentHierarchyMember.Level.Name = “Transition status”,
[Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
AND
IIF([Status].CurrentHierarchyMember.Level.Name = “Status”,
[Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
),
CASE WHEN
[Measures].[Issues history] > 0
THEN
DateDiffDays(
[Measures].[Issue status updated date],
Now()
)
END
))

How to calculate Days in transition status including the time in current status

The measure Days in transition status works for issues that already moved out from the status. Zeros might indicate an issue was moved out of the status shortly after adding to the status (or have transitioned to the same status). It does not include the time since the last status update. The following MDX formula (use it to define a new calculated member in Measures) will include in the calculation also days in the current status: -- days in transition status when issue was in this status in …

[Dilan]

HI @gerda.grantina

i managed to get the below suggestive query working Avg days between two statuses - #4 by martins.vanags

isit possible to figure out the workdays as this query is only showing days?

[gerda.grantina]

Hi @Dilan
If you want to calculate Workdays instead of Days, you can replace the function DateDiffDays() to DateDiffWorkdays() in the formula.

Gerda

[Dilan]

Awesome that worked thank you, another bit of help please

I found tickets that are skipping transitions as the workflow currently allows it. This means where issues havent gone to in progress before moving to done, the formula im using isnt calculating the lead time because its missing that initial status of In progress.

Would it be possible to enhance the query so it can handle that scenario also?

I.e.
Scenario 1 Calculate work days for issue type where its transitioned to Done from in progress for the month it transitioned to Done

Scenario 2 Calculate work days for issue type where its transitioned to Done but skipped inprogress transition for the month it transitioned to done.

[Dilan]

@eazyBI-Staff Hi All need some support i am using the following measure to get me Lead time for issue types between 2 statuses… However i have noticed if the issue has travelled inbetween those statuses multiple times. then the report is duplicating the entries for each month/date the status had moved from C to G. i.e.

Lets say in JAnuary it went from C to G and then in February it went from G to C back to G. Instead of reporting it in February with the lead time it is reporting the lead time when the status went from C to G in Jan and from then the status went from C in Jan to G in Feb.

Hope that makes sense.

Measure:
CASE WHEN
(
[Measures].[Transitions to status],
[Transition Status].[QA Complete]
) > 0
THEN
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[QA Complete]
) > 0
),
DateDiffWorkDays(
(
[Measures].[Transition from status first date],
[Transition Status].[Backlog],
[Time].CurrentHierarchy.DefaultMember
),
(
[Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember
)
)
)
END

[gerda.grantina]

Hi @Dilan ,
You can use DateInPeriod() to filter your measure by transitioning to QA Complete status the last date, so the measure is linked by this date to the Time dimension:

AVG(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod(
        ([Measures].[Transition to status last date],
        [Transition Status].[QA Complete],
        [Time].CurrentHierarchy.DefaultMember),
        [Time].CurrentHierarchyMember
      )
  ),
  DateDiffWorkDays(
    ([Measures].[Transition from status first date],
    [Transition Status].[Backlog],
    [Time].CurrentHierarchy.DefaultMember),
    ([Measures].[Transition to status last date],
    [Transition Status].[QA Complete],
    [Time].CurrentHierarchy.DefaultMember)
  )
)

best,
Gerda