Average Days in transition status till now?

Questions & Answers
,
1

Hey team, hope all is well!

I’m using average time in status in my report, I’m wondering how can I achieve a calculated measure for ‘Averate time in status till now’? I already have the ‘Time in status till now’, but not sure how to proceed from here.

Thanks a lot!

kindly,
Bianca

2

Hi @Bianca,

The easiest approach would be to include the “Time in status till now” within an average calculation, as is done here - Average days in transition status + average days in current status - #2 by martins.vanags. While it does produce correct results, the calculation would iterate through the same issues twice and prolong the report execution duration.

For performance reasons, I recommend a calculation that would consider these issues only once. Please try the formula below:

Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Transitions to status] > 0
  ),
  -- days since last transition to this status
  CASE WHEN
      -- for unresovled issues only
    IsEmpty([Issue].CurrentHierarchyMember.Get('Resolved at'))
  THEN
    IIf(
      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
      ),
      DateDiffDays(
        [Measures].[Issue status updated date],
        Now()
      ),
      NULL
    )
  END
  +
  -- days in transition status when issue was in this status in previous times
  IIF(
  [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]
  )
)

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures.

Let me know if you have any questions or suggestions.
Best,