Total time in status

Questions & Answers
1

Hello,

I want to calculate the time issues were in a specific status. In my case it is the status “In Progress”. I added a calculated member:

Sum(
  Filter(
    Descendants(
      [Issue].CurrentHierarchyMember,
      [Issue].[Issue]
    ),
    (
      [Measures].[Transitions from status],
      [Transition Status].[In Progress]
    ) > 0
    AND
    DateInPeriod(
      (
        [Measures].[Transition from status first date],
        [Transition Status].[In Progress],
        [Time].CurrentHierarchy.DefaultMember
      ),
      [Time].CurrentHierarchyMember
    )
  ),
  (
    [Measures].[Workdays in transition status],
    [Transition Status].[In Progress]
  )
)

It seems to work correctly but it is too slow. When I open the report it runs out of time. Is there a way to calculate this time more efficient?

2

Hi @rensch

You are on the right track with the measure “Workdays in transition status” and particular “Transition status” status.
You may go with this tuple alone, without iterating through all issues, because transition workdays are counted to the time period when the transition from the status has happened, you do not need additional condition for that

  (
    [Measures].[Workdays in transition status],
    [Transition Status].[In Progress]
  )

At the project level, the total workdays will be counted; at the issue level - only for this issue.

Tuples are very powerful and fast construction in MDX: you might watch a video presentation to learn more about them: Training videos on specific topics

Best,
Ilze / support@eazybi.com