Hi All,
I’m trying to get the the average In Progress vs Wait Time based on the transition statuses I’ve got. I’ve got “Ready for XYZ” statuses that are used as queues/wait time and I basically want to get the average days between them and the actual in progress statuses.
I first got the total workdays per Transition status (+ current open time) with the following formula:
Calc measure:Workdays in transition status + open
NonZero(
Cache(
– workdays in transition status when issue was in this status previously
[Measures].[Workdays in transition status] +
– workdays since last transition to this status
IIF([Measures].[Issues history] > 0,
DateDiffDays(
( [Measures].[Transition to status last date],
[Time].CurrentHierarchy.DefaultMember ),
Now()
), 0
)
)
)
Then I get the Average by using:
Calc measure: Avg Workdays in transition status
CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Workdays in transition status + open] /
[Measures].[Transitions from status]
END
This gets me the average workdays in each column, although it doesn’t give me zeros when certain cards skip columns. It only gets the time they actually sat there. So there might be a better way of getting the average workdays each card spent in each column. Any thoughts?
Then in a different chart, I’m grouping the columns in the Transition Status dimension:
Aggregate({
[Transition status].[In Development],
[Transition status].[In Review],
[Transition status].[In Test],
[Transition status].[Deployment]
})
However, if I use the calc measure “Workdays in transition status + open” it gives me the total days, if I use the calc measure “Avg Workdays in transition status”, it doesn’t give me the totals in the aggregation:
||Workdays in transition status + open|Avg Wkdays in Status Total|
|Value Adding Time|2058.058444|BLANK?|
|Waiting Time|628.1096989|BLANK?|
Calc measure: Avg Wkdays in Status Total
NonZero(
Sum(
Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
Not IsEmpty([Measures].[Avg Workdays in transition status])
),
[Measures].[Avg Workdays in transition status]
)
)
Thanks,