Average days in transition status + average days in current status

Questions & Answers
1

Hello,

I would be very grateful for some help on the following problem. I would like to implement the following formula ===>

(number of days spent in transition status / numbers of distinct issues that transitioned from the status) + (number of days spent in current status / numbers of issues in current status)

For example, I want to know the average time issues spent in the transition status Analysis + the average time issues still in the status Analysis have spent in that status, giving me an overall indicator about the time spent in Analysis.

This is what I’ve done so far. Unfortunately, the result is “infinity”.

NonZero(Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues history] >=0),
– for previous periods when issues was in particular status
[Measures].[Average days in transition status]
+
– time how long (til now) issue is in this status currently
(IIF([Measures].[Issues history] > 0,
DateDiffDays(
[Measures].[Issue status updated date],
Now()), 0)/[Measures].[Issues created count])
))

Thanks for your help and kind greetings to Latvia,

T.

2

Hi,

The infinity is because you are trying to divide by empty Issues created count.
You would need to add additional criteria for the second part of your calculation (Issues created count > 0 )
But I believe the way you are trying to calculate the total average results would not be correct anyway.

Actually, in this case, I would recommend a different approach:

  1. create a calculated measure (you can take the code example from our DEMO account) that would return “Days in transition status till now” for each issue.

  2. create another calculated measure that calculates the average results for these issues using the measure from step1

    NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Days in transition status till now] >= 0),
  [Measures].[Days in transition status till now]
 ))

Martins / eazyBI support

1 Like