We have several SLA bands that I need to report on monthly. I am tying to calculate the age of open (meaning not closed) issues where they were closed (not resolved) in 7 days or less. I have the following which is returning 'Failed to Execute query. Error message: ’
cache(
Count(
Filter(
–Iterate through all Issues in selected period
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
CASE
WHEN --Created Prior to the end of the period
DateBeforePeriodEnd([Issue].CurrentHierarchyMember.get(‘Created at’),
[Time].CurrentHierarchyMember)
THEN
CASE
WHEN --closed in this period
DateInPeriod([Issue].CurrentHierarchyMember.get(‘Closed at’),
[Time].CurrentHierarchyMember)
THEN --Evaluated age to close date
DateDiffDays([Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue].CurrentHierarchyMember.get(‘Closed at’)) <=7
ELSE
CASE
WHEN --Still Open
IsEmpty([Issue].CurrentHierarchyMember.get(‘Closed at’))
THEN – Evaluated age to end of period
DateDiffDays([Issue].CurrentHierarchyMember.get(‘Created at’),
DateAddDays([Time].CurrentHierarchyMember.NextStartDate,-1)) <= 7
–ELSE --This was closed previously and should not be counted
END
END
END
)
)
)
)