I've set up a table that shows me all the tickets that haven't met a pre-defined service level agreement based on priority (e.g. 1 day to resolve blockers, 7 days for trivial). So far I'm able to display this for the resolution time based on work days. I also had to split tickets up into 2 categories, i.e. tickets with a due date and those without. For tickets with a due date the SLA doesn't apply, but we check whether the due date was met or not.
What I want to accomplish, but so far haven't been able to, is to also subtract the time that a ticket has been put on hold (status is either hold, waiting or need more information) and thus only show the time that a ticket was being actively worked (any other status). How can I accomplish this?
For instance, to get the number of tickets that meet an SLA of 1 day for blockers:
CASE
WHEN [Priority].CurrentMember.Name MATCHES 'Blocker' THEN
NonZero(Count(
Filter(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Issues resolved] > 0 AND
[Measures].[Issues with due date] > 0) ,
DateDiffDays([Issue].CurrentMember.get('Due date'),
[Issue].CurrentMember.get('Resolved at')) <= 1
)
))
END
Can I modify this by including the time in certain statuses (hold+need more information+waiting) and if so how? Thanks!
