Background: Attempting to create a report that shows the length of time passed between going into “Status A” and going into “Status D” (just used A & D to make clear other status are passed through in the middle)
I am importing transition history ( Import issue change history including issue status workflow transitions, remaining estimated hours changes, and selected custom field changes.)
However I only seem to be able to use “Transition to status first date” for the status that the issue is currently in - I might be misunderstanding how it works, but shouldn’t this show the timestamp for when it went into any specified status?
Importing transition history is inevitable in your use-case.
There is no elegant way to calculate the the total time between two statuses unless it is one transition.
Assuming an Issue goes from A to B, you could try using tuples when creating a calculated measures
(
[Measures].[Days in transition status],
[Transition].[Status A => Status B]
)
A measure “Days in transition status” would return a time in days in the source status before it was changed.
But this approach would work only if the issue went directly from A to B (or from A to C, A to D) but not if there are statuses in between
I believe, in your case, you would need to use more sophisticated code to calculate the results.
Please use “Integer” formatting when creating your calculated measure with this code:
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[Status D]
)>0
),
Datediffdays(
(
[Measures].[Transition to status first date],
[Transition Status].[Status A],
[Time].CurrentHierarchy.DefaultMember
),
(
[Measures].[Transition to status last date],
[Transition Status].[Status D]
)
)
)
)