Median Number of Days in Retest

Questions & Answers
, ,
1

I’m trying to calculate the median number of days a ticket is in a Retest status before it is resolved. I used the following calculation and thought it was working until I realized that it is showing some tickets that are currently Unresolved and giving large numbers for tickets that should be near 0. Can someone help me figure out what I’m doing incorrectly?

Retest statuses = “Retest” and “Needs Remediation Validation”
Resolution = Resolved

Median(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
– Filter for resolved issues
[Measures].[Issues resolved] > 0
AND
– Check if the issue was ever in the specified statuses
([Measures].[Transitions to status],
[Transition Status].[Retest]) > 0
OR
([Measures].[Transitions to status],
[Transition Status].[Needs Remediation Validation]) > 0
),
– Calculate the days in the specified statuses
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Retest]),
([Measures].[Transition from status last date],
[Transition Status].[Retest])
)
+
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Needs Remediation Validation]),
([Measures].[Transition from status last date],
[Transition Status].[Needs Remediation Validation])
)
)

2

I think your filter logic might have little problem. You have A and B or C, maybe it should be A and (B or C)?
On the other hand, I think you can break things down into some 2 steps.
For example, create a Measure called Days in Retest for each case. You can use Case When clause for individual issue. Then manually verify a few to see if number is correct.
Then you can come back to create a Measure called Avg Days in Retest. Use Median plus descendant logic and refer back to Days in Retest

3

Hi @jbran00,

Thanks for posting your question!
@QiZhang thank you for the answer as well! Similarly to what @QiZhang commented, you could use the measure “Days in transition status” with the “Transition status dimension”
Try formula below and see if it returns the expected results?

Median(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
-- Filter for resolved issues in selected Time
  DateInPeriod([Measures].[Issue resolution date],
  [Time].CurrentHierarchyMember)
  ),
-- Calculate the days in the specified statuses
CASE
WHEN
  ([Measures].[Transitions to status],
  [Transition Status].[Retest]) > 0 OR
  ([Measures].[Transitions to status],
  [Transition Status].[Needs Remediation Validation]) > 0
THEN
([Measures].[Days in transition status],
[Transition Status].[Retest])
+
([Measures].[Days in transition status],
[Transition Status].[Needs Remediation Validation])

END
)

Best wishes,

Elita from support@eazybi.com