DateCompare

EAZYBI Returns 1 if the first date is greater than the second date; returns 0 if both dates are equal; returns -1 if the first date is less than the second date.

Syntax

DateCompare(Date_Expression1, Date_Expression2)

Arguments

Date_Expression1	MDX expression that returns a date.
Date_Expression2	MDX expression that returns a date.

You can also use a string expression that can be parsed with DateParse as any date argument.

Examples

Compare the Due date to the current day

The formula will return -1 if due date of the issue is before Today, 0 if an issue is due Today, and 1 if an issue due date is after Today. See example report Issues due and overdue in our demo account. The calculated measure Issues due in time uses the formula above to check if issue is not due yet and a similar comparison with resolution date for resolved issues before due date.

DateCompare([Issue].CurrentMember.Get('Due date'), 'Today')

Compare the Resolution date to a custom date

This formula compares a custom date to the resolution date of the issue currently in context.

DateCompare(
  [Issue].CurrentHierarchyMember.Get('Resolved at'), 
  '2025-05-15'
)

This formula compares a relative date to the resolution date of the issues currently in context.

DateCompare(
  [Issue].CurrentHierarchyMember.Get('Resolved at'), 
  '1 month ago'
)

Total overdue issues at the end of the period

This formula calculates the number of issues that were in an "overdue" state at the end of a selected Time period. It provides a historical "snapshot" of the overdue backlog. Use quarters, months, or weeks in the report Rows to see the number of Overdue issues at the end of each period.

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- Check if the Issue has a due date
    NOT IsEmpty([Measures].[Issue due date])
    AND
    -- Check that the due date is before the End current period
    -- End of period can be derived from the start date of the next period
    DateCompare(
      [Measures].[Issue due date],
      [Time].CurrentHierarchyMember.NextStartDate
    ) < 0
    AND
    -- Check that the resolution date is empty or after end of current period
    (
      IsEmpty([Measures].[Issue resolution date])
      OR
      DateCompare(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember.NextStartDate
      ) >= 0
    )
  )
)

Issues resolved after due date

This formula calculates the number of issues that were resolved within a given period but after their planned due date. Use quarters, months, or weeks in the report Rows to see the number of overdue issues resolved in each period. Note that this formula will not show overdue issues that are still not resolved.

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- Check that the issue was resolved in the current time period
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
    AND
    -- Check that the resolution happened after the due date
    DateCompare(
      [Measures].[Issue resolution date],
      [Measures].[Issue due date]
    ) > 0
  )
)