Count number of closed issue (those that are executed before the expiration (due date))

Hello.
Please help me, I need to count for each quarter:

A.Number of closed issue on time (those that are executed before the expiration (due date))
???

B.Number of issue not closed on time:
NonZero(count(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Issues with due date]>0 and
isempty([Measures].[Issues resolved]) and
DateCompare([Measures].[Issue due date],Now())<0
)
))

  1. % of closed tasks (those that are executed before the expiration (due date))

You are looking for calculations based on two issue dates. You would like to do an issue level calculation for this.

You can use functions DateInPeriod to filter issues with a particular date in a period (due date or closing date). You can use function DateCompare to compare two dates. Due date does not have a time only date. Closed date has time. If this is ok to count closed issues on due date as closed in time, I used DateWithoutTime function to remove Time from Closed date.

Issues closed in time

NonZero(Count(
Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- closed issues in period
  DateInPeriod(
    [Issue].CurrentMember.get('Closed at'),
    [Time].CurrentHierarchyMember) 
    AND
   -- issue does not have a due date
    IIF(
      IsEmpty([Issue].CurrentMember.get('Due date')), 1,
   -- or issue due date is bigger or equal to closed date
    DateCompare(
     [Issue].CurrentMember.get('Due date'),
     DateWithoutTime([Issue].CurrentMember.get('Closed at'))
     ) > -1)
    AND
    ( [Measures].[Issues created],
      [Time].CurrentHierarchy.DefaultMember ) > 0
)))

Due issues expired
The formula will count issues with due date in period unclosed till end of due date.

NonZero(Count(
Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
  -- issues with due date in period
  DateInPeriod(
    [Issue].CurrentMember.get('Due date'),
    [Time].CurrentHierarchyMember) 
    AND
    IIF(
      -- issue is not closed yet
      IsEmpty([Issue].CurrentMember.get('Closed at')), 0,
      -- issue is closed after due date
      DateCompare(
       [Issue].CurrentMember.get('Due date'),
       DateWithoutTime([Issue].CurrentMember.get('Closed at'))
       ) = -1)
    AND
    ( [Measures].[Issues created],
      [Time].CurrentHierarchy.DefaultMember ) > 0
)))

If you have both calculations on a place, you can address them in new calculations. The formula below uses default measure Issues closed and a calculated measure we made above with the name Issues closed in time.
% of closed in time
With the percentage calculation, you would like to avoid a division by zero. If there are closed issues, you can divide issues closed in time with all closed issues.

CASE WHEN
[Measures].[Issues closed] > 0
THEN
CoalesceEmpty([Measures].[Issues closed in time],0)/
[Measures].[Issues closed]
END

Function CoalesceEmpty will override the empty value with 0 and will give you 0% resolved in time if no issues are resolved in time.

Daina / support@eazybi.com

1 Like