Resolution Time deducting time spent in status

Questions & Answers
8

Hi @CarlyBooth ,

Good job! The DateCompare() function is a good option to satisfy your requirement. It is up to you to consider the two suggestions below, as they won’t affect the measure performance, rather improve the readability of the MDX formula.

I don’t think the formula requires the DateWithoutTime() and DateParse() functions. Also, you can try to sum the days issues spent in the particular statuses with Sum(). See the suggested formula below:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
    )
    AND
    DateCompare(
      [Measures].[Issue created date],
      '2020-01-01'
    )>0
  ),
  [Measures].[Total resolution days]
  -
  Sum(
    {[Transition Status].[Draft],
    [Transition Status].[Live],
    [Transition Status].[Campaign Outline Due],
    [Transition Status].[Production Brief Due]
    },
    ([Measures].[workdays in transition status],
    [Time].CurrentHierarchy.DefaultMember)
  )
))

Best,
Roberts // support@eazybi.com