Hi,
There could be a more efficient solution for the Reaction time calculation if the Assigned status always is followed by the In Progress. Then the reaction time is all the time spent in the Assigned status, and the formula for such measure can be created with the tuple:
([Measures].[Days in transition status],
[Transition Status].[Assigned])
You might wish to use another measure for calculating the average:
([Measures].[Average days in transition status],
[Transition Status].[Assigned])
It is also possible to calculate the time in several statuses by creating aggregated members in the Transition status dimension.
A more general alternative is to calculate the day’s difference between two dates of status transitions:
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Assigned]),
([Measures].[Transition to status first date],
[Transition Status].[In Progress])
)
Such formula works with the issues dimension and must be wrapped in the sum or average iterating through the set of issues:
Avg(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[transition from status],
[Transition Status].[Assigned])>0
),
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Assigned]),
([Measures].[Transition to status first date],
[Transition Status].[In Progress])
)
)
There could be variations of such formula depending on the report design, especially if the Time dimension is used.
Kindly,
Janis, eazyBI support