Hi Team,
I have define new calculated members to get the age of tickets,but I’m getting negative value in output. Below is the query
NonZero(Avg(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Open issues] > 0 OR [Measures].[Issues closed] > 0),
Case when isempty([Measures].[Issue closed date])
then
Case when
DateBeforePeriodEnd(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember) AND DateDiffWorkDays(
[Time].CurrentHierarchyMember.NextStartDate, Now())>0
then
DateDiffWorkDays([Measures].[Issue created date],
[Time].CurrentHierarchyMember.NextStartDate)
else
DateDiffWorkDays([Measures].[Issue created date],Now())
end
else
DateDiffWorkDays([Measures].[Issue created date],
[Measures].[Issue closed date])
end ))
I would like to know where I’m wrong / where i need to add up some stuff
You are using two different ways for filtering issues, that could lead to inconsistent results in some cases.
You are using measures Open issues and Issues closed. Measure open issues stop counting any issue as open based on resolution date. If you do not have resolution date or you have a gap between resolving an issue and closing an issue the formula might pick the wrong set of issues.
You would like to use either metrics based on Resolution date in the formula or create a new calculated measure Open issues by closed or similar using the formula of Open issues and substituting metrics based on resolution date with metrics based on the closing date.
I also noticed that the formula calculates the cumulative age for issues without closing date and total time for closed issues. If you would like to calculate the cumulative time till closing or resolution you can check out quite the similar formula Average days till resolution or period end in our example report from demo account:
https://eazybi.com/accounts/1000/cubes/Issues/reports/50084-average-age-till-resolution-report
This formula calculates the cumulative age for open issues till resolution or till the period end.
Daina / support@eazybi.com