Issue resolution days (lead time) reporting

eazyBI for Jira

This example will show how to use several calculated measures for issue resolution days reporting.

The first Average resolution days measure is default calculated measure which shows average resolution time (between creation date and resolution date) of resolved issues in selected Time period. It uses simple formula:

CASE WHEN [Measures].[Issues resolved] > 0 THEN
  [Measures].[Total resolution days] / [Measures].[Issues resolved]
END

The next Average resolution workdays measure is default calculated measure which shows average resolution workdays (between creation date and resolution date) of resolved issues in selected Time period. It uses simple formula:

CASE WHEN [Measures].[Issues resolved] > 0 THEN
  [Measures].[Total resolution workdays] / [Measures].[Issues resolved]
END

Median resolution days measure calculates median of resolution days over all issues resolved in period:

Median(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ([Measures].[Issues created],
       [Time].CurrentHierarchy.DefaultMember) > 0
  ),
-- resolution (lead time) time in days. You can calculate the same in workdays using DateDiffWorkdays instead of DateDiffDays
 DateDiffDays(
        [Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at'))
)

In this formula, the condition DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'), [Time].CurrentHierarchyMember) AND ([Measures].[Issues created],[Time].CurrentHierarchy.DefaultMember) > 0 is used for performance optimisation instead of the simpler [Measures].[Issues resolved] > 0 condition (at first we are checking if the Resolved at property of the issue is within the current Time dimension period).

Issues resolved <10d measure filters and counts only those issues which are resolved in less than 10 days.

The measure uses dimension Resolution interval. You would like to select interval dimensions for import your Jira data import in Additional options tab.

Please set intervals in Resolution interval dimension you would like to use for reporting - representing resolution interval range. 

([Measures].[Issues resolved],
[Resolution interval].[000 - 009])

Intervals could be different in any account. Use autocomplete and select the one that matches your resolution interval setup.

And Issues resolved >10d  uses Sum formula over any other intervals, Except none and 10 day interval to count issues resolved in more than 10 days :

NonZero(SUM(
  Except(
    [Resolution interval].[Resolution interval].Members,
    {
    [Resolution interval].[(none)],
    [Resolution interval].[000 - 009]  
    }
  ),
  [Measures].[Issues resolved]
))

If you do not have interval dimension in your account, the same could be done with issue level calculation. Those calculation could work slower in accounts with large issue set.

Issues resolved < 10d measure filters and counts only those issues which are resolved in less than 10 days.

CASE WHEN [Measures].[Issues resolved] > 0 THEN
  NonZero(Count(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
        [Time].CurrentHierarchyMember) AND
        ([Measures].[Issues created],
         [Time].CurrentHierarchy.DefaultMember) > 0 AND
      DateDiffDays(
		[Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at')) < 10
    )
  ))
END

You can use modified version of the formula for a different number of days or for the number of working days. The NonZero function will return empty result instead of 0.

And Issues resolved >10 days uses similar formula to count issues resolved in more than 10 days:

CASE WHEN [Measures].[Issues resolved] > 0 THEN
  NonZero(Count(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod([Issue].CurrentHierarchyMember.get('Resolved at'),
        [Time].CurrentHierarchyMember) AND
       ([Measures].[Issues created],
        [Time].CurrentHierarchy.DefaultMember) > 0 AND
      DateDiffDays(
		[Issue].CurrentHierarchyMember.get('Created at'),
        [Issue].CurrentHierarchyMember.get('Resolved at')) >= 10
    )
  ))
END