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