Average days in status based on total time in status

[James Weaver]

I'm trying to calculate the average length of time issues spent in Triage status on a weekly basis.

The measure I’m currently using to calculate the average is giving the correct figure based on dividing the total number of days by the number of issues that moved out of Triage, however in some cases the number of days is incorrect.

For example, I have 3 issues that moved out of "Triage" status in one week (W33, Aug 06 2018) under Workstream = Authentication & Fraud:

1 = 0.00 days

2 = 0.96 days

3 = 59.99 days

The average days in triage status for issues that week is therefore approx. 20.3 days, this is correct however issue 1 in the above example has been moved out of Triage status twice, once after 25 days and once after 10 seconds.

I need the calculation to count both the 25 days AND the 10 seconds the issue spent in Triage status

When calculating the average it seems to only be taking the most recent transition into account instead of the total time it has spent in Triage status regardless of transitions. Is there a way to include total time an issue has spent in a status in all transitions before calculating the average time all issues that week spent in a particular status.

Below is the measure I have been using.

Average days in transition status by count:

CASE WHEN [Measures].[Transitions from status issues count] > 0 THEN
[Measures].[Days in transition status] /
[Measures].[Transitions from status issues count]
END

Thanks,