Date Compare with Buffer of 7 days

Questions & Answers
1

Hi everyone,
Hope you are doing well.

I am currently using the following calculated member formula to check if one Issue has been closed within or outside the due date.

CASE WHEN
DateAfterPeriodEnd(“Today”, [Time].CurrentHierarchyMember)
OR
DateInPeriod(“Today”, [Time].CurrentHierarchyMember)
THEN
NonZero(SUM(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– filters all issues with due date in period
DateInPeriod(
[Issue].CurrentMember.get(‘Due date’),
[Time].CurrentHierarchyMember)
AND
– filter to see if due date is before resolution date or till today
IIF(
IsEmpty([Issue].CurrentMember.get(‘Resolved at’)),
DateCompare([Issue].CurrentMember.get(‘Due date’), “Today”) > 1,
DateCompare(
[Issue].CurrentMember.get(‘Due date’),
DateWithouttime([Issue].CurrentMember.get(‘Resolved at’))
) > 1)
),
( [Measures].[Issues created count],
[Time].CurrentHierarchy.DefaultMember )
))
END

I am just checking if it was closed after the due date or not.
Now, I need to enhance this calculation to add a “buffer” on the closing - which will allow the team to close the issue within 7 days (for more or less) than the due date and still consider it as “within” the due date.

Do you have any guesses or tips on how can I achieve it?

Thanks a lot in advance!

Best regards,
Augusto

2

Hi @guto255,

If you want to extend the allowed timeframe by 7 days for the issue completion, you might add these extra days to the due date of today.
The original expression had some problems, as the DateCompare() function can never return a value greater than +1.

The updated expression for “Issues still in time” might be as follows.

CASE WHEN
 DateAfterPeriodEnd('Today', [Time].CurrentHierarchyMember)
  OR
 DateInPeriod('Today', [Time].CurrentHierarchyMember)
THEN
NonZero(SUM(
 Filter(
  Descendants(
   [Issue].CurrentMember, [Issue].[Issue]),
-- filters all issues with due date in period
  DateInPeriod(
   [Issue].CurrentMember.get('Due date'),
   [Time].CurrentHierarchyMember)
 AND
-- filter to see if due date+7 is before resolution date or till today+7
 IIF(
  IsEmpty([Issue].CurrentMember.get('Resolved at')),
   DateCompare(
    [Issue].CurrentMember.get('Due date'),
    '7 days from today') < 1,
  DateCompare(
   DateAddDays(
    [Issue].CurrentMember.get('Due date'),
    7),
   DateWithouttime([Issue].CurrentMember.get('Resolved at'))
   ) < 1)
  ),
 ([Measures].[Issues created count],
  [Time].CurrentHierarchy.DefaultMember)
))
END

Regards,
Oskars / support@eazyBI.com