This might be a bit special as I asked around and no one seem able to find a solution… we need a table to show how many days were used in transition status for each of the status assignee; this can be achieved using below configuration
Columns = Measures = Days in transition status
Rows = Assignee or Transition Author
Average days = [days in transition status]/[transitions from status]
But this does not include the issues which are still in the corresponding status, have not moved onto next status; for e.g. if an issue is left in OPEN over x days, it will not be included in the table.
How can we include the issues data left in the status?
If that is possible, then the “Average days = [days in transition status]/[transitions from status]” would be invalid as there is no “transitions from status” for that issue… how can we deal with it now?
It sounds like you would also need to filter your report by “Transition Status” dimension value to calculate time in status.
However I don’t find where do you filter the report by some status.
Anyway, please try the following code (when defining a new calculated member in “Measures” dimension) to calculate days in transition status including the current status:
Cache(Sum(Generate(
ChildrenSet([Transition Status].CurrentMember),
Descendants([Transition Status].CurrentMember,
[Transition Status].[Transition Status])),
[Measures].[Days in transition status] +
Sum(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issues history] >=0
),
CASE WHEN [Transition Status].CurrentMember.Name = [Measures].[Issue status]
THEN DateDiffDays([Measures].[Issue status updated date], Now())
END
)))
Such calculation would work together with “Assignee” and “Transition status” dimensions.
Later you could divide this measure by another measure “transitions to status” to get the average result.
