Resolution Time deducting time spent in status

Questions & Answers
5

Hi @CarlyBooth ,

You could achieve a slight increase in performance with the formula below:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
    )
  ),
  [Measures].[Total resolution days]
  -
  (
    [Measures].[Days in transition status],
    [Transition Status].[Draft],
    [Time].CurrentHierarchy.DefaultMember
  )
  -
  (
    [Measures].[Days in transition status],
    [Transition Status].[Live],
    [Time].CurrentHierarchy.DefaultMember
  )
))

It replaces the DateDiffDays() function retrieving the total resolution days with the hidden measure “Total resolution days”.

You are right. The calculation could be offloaded during the import with a JavaScript calculated custom field. Please have a look at some examples in other community posts. One example suggesting importing this as an interval dimension and a measure is found here - Issue Resolution histogram excluding time blocked - #2 by vanessa.equip. Try to adapt it to fit your use case.

Best,
Roberts // support@eazybi.com