Count all issues with breached Due Date for period of time regardless of their statuses

Questions & Answers
, ,
1

Hi colleagues,

I need to find and count all breached (by duedate) issues by responsible persons for some period of time.
I choose time (“page” in my report) and want to see such table: responsible person | all (open+resolved) issues with due date in chosen period | all (open+resolved) issues breached in this period.

When we consider the issue is breached:

  1. If the issue was resolved in the chosen time period, then we just need to compare due date and resolution date, so it is breached only if DueDate < ResolutionDate
  2. If the chosen period of time is in past (last month, for example) and issue wasn’t resolved by it’s end, then we need to compare due date with the end of time period, so the issue is breached if DueDate < StartOfLastDayOfThePeriod
  3. If the chosen period of time is current and ends somewhere in the future (for example, this month) and issue hasn’t been resolved by now, then we need to compare due date with the start of current day, so the issue is breached if DueDate < StartOfToday
    (Yes, if the issue’s Due Date is today and it’s resolved tonight - that’s fine)

We’re already counting the resolved issues with due dates and do it in these two steps:

  • the first calculated member just check “breach status” of resolved issue with this formula: IIF(DateDiffDays([Measures].[Issue due date], [Measures].[Issue resolution date])> 1, 1, 0)
  • and the second one count issues with “breached” (1) status by responsibles (in rows) with this formula:
    Sum(Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]), DateInPeriod([Issue].CurrentMember.get(‘Due date’), [Time].CurrentHierarchyMember) AND [Measures].[Breached DueDate] > 0 AND [Measures].[Issue Responsible] = Responsible.currentmember.name AND ([Time].CurrentHierarchy.DefaultMember, [Measures].[Issues resolved]) > 0), [Measures].[Breached DueDate])

But now we need to add also active issues which has been already breached to have the full picture regardless of issues statuses (due date of issue is usually set at start and can’t be moved by responsible, so if it’s breached - it’s breached), and I can’t come up with the right formula.

Thanks in advance for your help.

2

You would like to check out demo account and this example report Issues due and overdue in particular.

This report counts issues based on due date: open overdue issue, issues with due date open/resolved in time/and not in time.

I hope this report will help you cover all your scenarios.

Daina / support@eazybi.com