Stop the count for number of days if overdue issue is closed

Questions & Answers
1

Currently, I’ve set up a formula that counts the Total Overdue Days of Issues per quarter. But I wanted to stop the count of days once the issues are in closed/resolved state. How can I do this with my formula below?

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentMember.Get(‘Due date’),
[Time].CurrentHierarchyMember
)
),
CASE WHEN
[Measures].[Overdue Issues] > 0
THEN
– total time from issue due date till now
DateDiffDays(
[Issue].CurrentMember.get(‘Due date’),
Now()
)
END
)

2

Hi @jeno ,

I recommend adding a condition with IIf() to determine whether the overdue issue is resolved or not and then determine the duration accordingly. For example, the formula could look similar to the one below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentMember.Get('Due date'),
      [Time].CurrentHierarchyMember
    )
  ),
   CASE WHEN
   [Measures].[Overdue Issues] > 0
   THEN
  -- total time from issue due date till now
  IIf(
    -- resolution date empty?
    IsEmpty([Issue].CurrentHierarchyMember.Get('Resolved at')),
    -- overdue till now
    DateDiffDays(
      [Issue].CurrentMember.get('Due date'),
      Now()
    ),
    -- overdue till resolution date
    DateDiffDays(
      [Issue].CurrentMember.get('Due date'),
      [Issue].CurrentHierarchyMember.Get('Resolved at')
    )
  )
   END
)

See more details on the IIf() statement here - IIf statement.

Best,
Roberts // support@eazybi.com