How to create a Average Age of Open Issues & Excluding the Time Spent on one of the Stages

Questions & Answers
, , ,
1

Hi EazyBI community,

Looking for some help on editing the “Average Age of Open Issues” calculated member formula, but exclude the time spent on one of my team’s stages (“Response Pending”. Does anyone know how to edit this current formula to do that?

Any help would be greatly appreciated!

CASE WHEN [Measures].[Open issues] > 0 THEN
Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filter open issues in period using issue properties Created date and Resolution date only
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember)
),
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
– cumulative age of each issue for any period till end of period or till today
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN DateDiffDays([Issue].CurrentMember.get(‘Created at’),
Now())
ELSE DateDiffDays([Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember.NextStartDate)
END
END
)
END

2

Hi, @nwhill

Welcom to the eazyBI community.

Please consider creating a new member (read more here: Calculated members in other dimensions) in the Status dimension that contains all the statuses you are interested in. Please use Aggregate - Aggregate

If it is easier to remove the statuses from the list, then please use Except:Except

After creating the member in the Staus dimension, please use a tuple. Tuple is the simplest way to get the slice of data. Useful when combining measures with specific context (like time periods, projects, statuses, etc). Read more about tuple here: Tuple

The pseudocode of tuple should look something like this:

([Measures].[your measure - Average Age of Open Issues],
[Status].[your status member - Except Response Pending, for example])

Kindly,
Ilze