Subtract from Total Resolution Days

Avg Resolution Days is using the hidden measure of Total Resolution days. I’d like to recreate the Created to Resolved report from the jira demo but add a custom measure of Avg Resolution Days without the time spent in a specific status (draft)

something like:
CASE WHEN [Measures].[Issues resolved] > 0 THEN
([Measures].[Total resolution days] - ([Measures].[Transitions to status].[To Do]+
[Measures].[Transitions from status].[Draft]))
/ [Measures].[Issues resolved]
END

not sure what the MDX would be

You are on the correct path. You would like to use historical measures representing past statuses.

However, I would suggest using measure Days in transition status for both statuses To Do and Draft. I would suggest creating the calculation on the issue level:

NonZero(AVG(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    -- filter only resolved issues in period
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get("Resolved at"),
      [Time].CurrentHierarchyMember
    )),
    CASE WHEN  
    [Measures].[Issues resolved] > 0
    THEN
    -- total time from issue creation till resolution
    DateDiffDays(
      [Issue].CurrentHierarchyMember.Get("Created at"),
      [Issue].CurrentHierarchyMember.Get("Resolved at")
    )
    -
    -- time in statuses you want to excude
    Aggregate(
    {
    [Transition Status].[To Do],
    [Transition Status].[In Review]
    },
    ([Measures].[Days in transition status],
     [Time].CurrentHierarchy.DefaultMember)
    )
    END
))

Daina / support@eazybi.com

1 Like