I’m trying to replicate the JIRA Average Age report in eazybi, however the data is not matching- I’ve tried to display week on week data (and it shows different from JIRA).
I’ve tried using the Average Age of Open Issues Measure from the below report: https://eazybi.com/accounts/1000/cubes/Issues/reports/50084-average-age-of-open-issues
I feel the reason behind the data mismatch is because the Open Issues Measure considers all unresolved issue states and in JIRA we are not considering “New” State.
Is there a way I can filter out the New State from the Open Issues Measure and then use it in the Average Age of Open Issues Filter.
You are right Open issues includes all unresolved issues regardless of Status.
In your case, you would need to use the adjusted code for calcualted measure.
Try this code:
CASE WHEN [Measures].[Open issues] > 0 THEN
Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Open issues] > 0
AND
DatebeforeperiodEnd(
(
[Measures].[Transition from status first date],
[Transition Status].[New]
),
[Time].CurrentHierarchyMember)
),
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN DateDiffDays(
(
[Measures].[Transition from status first date],
[Transition Status].[New],
[Time].DefaultMember
),
Now()
)
ELSE DateDiffDays(
(
[Measures].[Transition from status first date],
[Transition Status].[New],
[Time].DefaultMember
),
[Time].CurrentHierarchyMember.NextStartDate
)
END
)
END
Note that it would start counting age as soon as the issue is moved out from “New” status and may still return age if issue suddenly is moved back to “New” status for some reason.