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.
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,