How to Calculate Meeting a Due Date with a Generic Date Field

Hello there. I am using data from JIRA and need to calculate the total number of issues that do not meet a due date using a generic field.

Something like this:

Generic Date1 <= Due Date

The problem I am running into is the formula I am using does not sum the issues that have a Generic Date outside of the Time which is the previous month.

Example: Generic Date1 = 9/30/2022, Due Date 10/5/2022. Report looks at previous month as a Time Dimension.

Pasting the formula below:
NonZero(Sum(
Filter(
– iterate through set of issues
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
– apply filter criteria to each issue
–/*
DateInPeriod(
[Measures].[EmptyGenericDate1],
[Time].CurrentHierarchyMember
) or
DateInPeriod(
[Measures].[Issue due date],
[Time].CurrentHierarchyMember
)–*/

– [Measures].[Issue Generic Date1] <= [Measures].[Issue due date]
),
– numeric expression - sum of relevant issues
[Measures].[Issues with Generic Date1]

))

I am using another calculated field called “EmptyGenericDate” to update the the Generic Date to be 1 day past the due date so it counts it correctly (as blanks should be treated as not meeting the due date).

Please help. Not sure if I may be overthinking or not.

Hello @ELVHLTH-blylaust12,

You were close to the result and got slightly too far.
The main question here is - where do you want to attribute the issues?

Do you want them to count against the “Due date” or the “Generic Date1”?

Then you might keep just one check for DateInPeriod and use the measure “Issues with … date” to count the issues.

Please see the below example for counting issues with “Generic Date1” in the current month or other Time dimension members.

NonZero(
 Sum(
  Filter(
-- iterate through set of issues
    DescendantsSet([Issue].CurrentHierarchyMember,[Issue].[Issue]),
-- apply filter criteria to each issue
  DateInPeriod(
   [Measures].[Issue Generic Date1],
   [Time].CurrentHierarchyMember)
  ),
-- numeric expression - sum of relevant issues
--only sum when generic date less or equal with due date
  IIF(
   (CoalesceEmpty(
      DateToTimestamp([Measures].[Issue Generic Date1]),
--if empty - put it after due date -> failed
      DateToTimestamp([Measures].[Issue due date])+87000)
  -
--adding 24h until the end of day for due date
    (DateToTimeStamp([Measures].[Issue due date])+86399)
    <0,
--check against other report context
   [Measures].[Issues with Generic Date1],
--if exceeded due date then zero
   0)
))

You might attribute issues to the Time dimension at the “Generic Date” or “Due date” by changing the measure on lines 8 and 23.
You might also adjust the number on line 20 to control whether the timestamp of “Generic Date” during the day is considered as met or breached the “Due date”.

Regards,
Oskars / support@eazyBI.com