Is there a way to exclude statuses from the workdays in transition calculation?

Questions & Answers
1

I am looking to get to the total time that was actually spent working on an issue, but the Workdays in transition status calculation includes time in the backlog and the up next queue. Is there a way to exclude specific statuses from the calculation?

1 Like
2

+1
option to exclude certain status not just backlog but approvals/demos

3

I tried filtering out those statuses, but it did not affect the totals on Workdays in transition status. Do you mean exclude it as part of a calculated measure?

4

Yes
Ideally have ‘Status’ as a dimension on the page to select the status to be considered for the calculations

5

Filtering does not appear affect the numbers that I was looking for. I did some searching around the community and found a formula that I modified. The only remaining issue that I have is that our developers have two different statuses for work in progress. Anyone know how to aggregate the two statuses as part of this formula.

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[Done]
)>0
),
DateDiffWorkdays(
  (
    [Measures].[Transition to status first date],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember
    
  ),
  (
    [Measures].[Transition to status last date],
    [Transition Status].[Done]
  )
  
)
)
)
1 Like
6

Ack! One more. I want to exclude time in the Blocked status, too. I feel like I’m just needy at this point. :grinning:

7

Hi, @David_Kepley

Excellent to see that you found the formula for the calculation of the workdays between several status transitions.

There is another approach for calculating the time in several statuses, so you can include or exclude statuses in a more flexible way.

First, you can create an aggregated member in the Transition status dimension and list all the statuses you need to cover. The aggregated member could look like this:

Aggregate({
[Transition Status].[In Progress],
[Transition Status].[To Do]
})

image
image1536×1074 118 KB

Once you have such a member in the Transition status (let’s call it “Cycle statuses”), you can use the standard Average days in transition status:

([Measures].[Average workdays in transition status],
[Transition Status].[Cycle statuses])

Kindly,
Janis, eazyBI support

1 Like