Count of issues with an age of less than 7 days

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
)
)
)
)

Hi @EdP,

First, try to calculate the count of issues that were closed within seven days. Please check out the formula below and have a look if it is what you are looking for:

NonZero(Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    (
    --Not closed
        (
            [Measures].[Open issues] > 0
            --IsEmpty([Measures].[Issue closed date])
            AND
            DateDiffDays(
                [Measures].[Issue created date],
                now()
            ) <= 7
        )
        OR
    --Closed
        (
            [Measures].[Issues closed] > 0
            --NOT IsEmpty([Measures].[Issues closed date])
            AND
            DateDiffDays(
                [Measures].[Issue created date],
                [Measures].[Issue closed date]
            ) <= 7
        )
    )
  )
))

If this meets the conditions, then you can alter the formula to use the Sum() function instead of Count() and use the measure [Measures].[Total closing days] as the numeric expression for the Sum() function - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/sum.

Kind regards,
Roberts // eazyBI support