Create measure to filter issues where 'Issue Progress end date' is either blank or higher than 'Sprint start date'

[Peter_Young]

Hi All,

I’m trying to create a measure to filter issues where ‘Issue Progress end date’ is either blank or higher than ‘Sprint start date’

My attempt:

CASE
WHEN
([Measures].[Issue Progress end date] = 0) 
OR
([Measures].[Issue Progress end date]>[Measures].[Sprint start date])
THEN
[Measures].[Issue Progress end date]
END

I think there is some value type error with the dates not being compatible by am not sure how to address it.

This is what returns in the table: “#ERR: mondrian.olap.fun.MondrianEvaluationException: Expected value of type NUMERIC; got value ‘Thu Feb 15 15:18:36 UTC 2024’ (DATETIME)”

Any help would be appreciated

[roberts.cacus]

Hi @Peter_Young,

Use the DateCompare() function to compare two dates. To check whether an issue property value is empty, use the IsEmpty() function. With the Issue dimension in report rows and the Sprint dimension in pages, the formula could look similar to the one below:

CASE WHEN
IsEmpty([Measures].[Issue due date])
THEN
1
WHEN
DateCompare(
  [Measures].[Issue due date],
  [Measures].[Sprint start date]
) > 0
THEN
1
END

And the report:

Update the formula to take into account your date property instead of the issue due date.

Best,
Roberts // support@eazybi.com