Avg days between two statuses

Questions & Answers
4

@joejames

Try this code:

CASE WHEN 
(
[Measures].[Transitions to status],
[Transition Status].[Waiting for Production]
) > 0 
THEN
 AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      (
        [Measures].[Transitions to status],
        [Transition Status].[Waiting for Production]
      ) > 0 
    ),
    DateDiffDays(
      (
       [Measures].[Transition to status first date],
       [Transition Status].[In Progress],
       [Time].CurrentHierarchy.DefaultMember
       ),
      (
       [Measures].[Transition to status last date],
       [Transition Status].[Waiting for Production]
       )
    )
  )
END

It will calculate the average difference between the very first transition to In progress until the last transition to “Waiting for Production” for issues that went to the Waiting for Production status in the report context.

This solution is not great for cases where one issue travels between both these statuses multiple times.

Martins / eazyBI