Exclude issues in certain Transition Status for longer than X amount of time

I am trying to create a report on hours worked based on “Transition Author” (Row), “Transition Status” (Page) and “Hours in transition status” (calculated measure based on “Days in transition status” measure) as the team does not log hours worked separately.

Transition statuses considered to be hours worked are “In Progess” and “Validating”.

I have managed to create a simple report by creating a calculated member in the Transition status dimension which just adds the transition status “In Progress” and “Validating” and named it “Worked”, which works fine.
(I realize I can also just select “In Progess” and “Validating”).

This has the drawback that it includes issues that have been accidentally left in either status for a longer time.
So the desire would be to exclude any issues that were in “In Progress” or “Validating” for longer then X hours.

I have attempted various SUM/Filter calculations to create calculated members in the Measures or Transition Status dimension, but had not had any success.

I had partial success with the below as a calculated member in the Transition Status dimension, but this will add up all transition statuses that are less then 2 hours.

Descendants([Issue].Currentmember, [Issue].[Issue]),
[Measures].[Hours in transition] < 2))"

Any help would be appreciated.

I think I finally had the eureka moment and added the calculated member in the issues dimension and it seems to work now!

1 Like

can you share the formula hours in transition and the resolution ?