Count of overdue issues in time dimension, split by status at the time they were overdue

zane.baranovska · July 4, 2024, 12:25pm

The calculation for “Overdue issues in period” is complex as it iterates through all issues and for each checks several conditions. Adding the issue history to it adding a bit more complexity.

Here is one more variation you can try. Take “Overdue issues in period” expression as base and make three adjustments.

return “Issues history” measure instead of number 1 for overdue issues (one place, at the end of code)
add “Transition Status” default member to the tuple expressions with measure “Issues created” (two places)

The updated expression might look like this.

-- formula will calculate overdue issues by cumulative time from due date
-- for each period if issue is not resolved comparing due date and end of period
-- on period when issue is resolved comparing due date and resolution days
-- formula calculates overdue time and if it is bigger than 0 then it wil count an issue as overdue in period
CASE WHEN
  DateAfterPeriodEnd(
    'today',
    [Time].CurrentHierarchyMember.PrevMember)
THEN
NonZero(Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- filter by issue properties to get only open or resolved issues in period

      --if today's due date is not overdue yet
      DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Due date'),
              [Time].CurrentHierarchyMember.PrevMember)            
     AND( NOT DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Resolved at'),
              [Time].CurrentHierarchyMember)
     or 
     DateInPeriod(
              [Issue].CurrentMember.get('Resolved at'),
              [Time].CurrentHierarchyMember))
  ),
  CASE WHEN
  Cache(
    -- day difference to due date for resolved issues on period when issue was resolved
    CASE WHEN DateInPeriod(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ( [Measures].[Issues created], 
        [Transition Status].CurrentHierarchy.DefaultMember,
        [Time].CurrentHierarchy.DefaultMember ) > 0 
    THEN
      DateDiffDays([Issue].CurrentMember.get('Due date'),
        DateWithoutTime(
        [Issue].CurrentMember.get('Resolved at')
        )
        )
    --  day difference to due date for open issues for all periods when isses where open
    WHEN DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Due date'),
      [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember) AND
      ( [Measures].[Issues created],
        [Transition Status].CurrentHierarchy.DefaultMember,
        [Time].CurrentHierarchy.DefaultMember ) > 0
    THEN
      -- not current time period, till end of period is calculated
      CASE WHEN DateCompare(
          [Time].CurrentHierarchyMember.NextStartDate, Now()) < 0
      THEN
        DateDiffDays(
          [Issue].CurrentMember.get('Due date'),
          [Time].CurrentHierarchyMember.NextStartDate
        )
      ELSE
      -- current time period, till now
        DateDiffDays([Issue].CurrentMember.get('Due date'),
          Now()
        ) 
      END
      -- day difference above 0 indicates how overdue this issue is
    END) > 0 
  THEN [Measures].[Issues history] 
  END
))

END

Then you can use the calculation with the “Transition Status” dimension to see the statuses of overdue issues at the end of period.

Best,
Zane / support