Workdays in transition status before first change status

Hi,

There is an example issue’s statuses change:
To do → In progress → On hold → In progress → On hold → In progress → In tests → In progres → …

I would like to create a report in which I’ll see how many workdays the issue was in status “In progress” before first going to “In tests” status - without the time in “on hold” status.

How can I use “workdays in transition status” measure and how can I substract all the times when the issue was in status “on hold” before going to “in tests”? The issue can go into “on hold” status during “In progress” 0 or more times - there is no rule.

Hi @MarZ,

With the Issue dimension in the report rows, you can define the following calculated measure:

CASE WHEN
  ([Measures].[Transitions from status],
  [Transition Status].[In progress]) > 0
THEN
  CASE WHEN
    ([Measures].[Transitions to status],
    [Transition Status].[In tests]) > 0
  THEN
    Sum(
      {
        PreviousPeriods(
          [Time].[Day].DateMember(
            ([Measures].[Transition to status first date],
            [Transition Status].[In tests],
            [Time].CurrentHierarchy.DefaultMember)
          )
        ),
        [Time].[Day].DateMember(
          ([Measures].[Transition to status first date],
          [Transition Status].[In tests],
          [Time].CurrentHierarchy.DefaultMember)
        )
      },
      ([Measures].[Workdays in transition status],
      [Transition Status].[In progress])
    )
  ELSE
    ([Measures].[Workdays in transition status],
    [Transition Status].[In progress])
  END
END

The calculation determines whether the issue has any transition from the “In progress” status. It will again check for a transition into the “In tests” status if it does. Without transitions into the “In tests” status, all workdays in the “In progress” status will be returned.
Suppose an issue has the transition into the “In tests” status. The calculation will determine the first date of that transition for the particular issue and retrieve a “Day” level member in the Time dimension matching that date. Then, with the help of PreviousPeriods() function, the calculation will sum the value for the “Workdays in transition status” for all occurrences before the first transition date into “In tests”.

There could be some deviations if In progress->In tests->In progress->On hold transitions are made on the same day.

In the example below, I consider the workdays in the “In Progress” status until the first transition into “Review”. In some cases, the value is empty compared to the “Total workdays in progress” because the first transition into “Review” happened before “In Progress”:

Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Best,
Roberts // support@eazybi.com