how to compare date column in measure?

[Anonymous]

IIF(Fields!Date.Value ="12/31/9999", "END",Fields!Date.Value)

Is is possible to create measure with above condition?As my source is tabular model, i can't use calculated column for above condition.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

"END" is a Text string, while Fields!Date.Value is a Date type value. Measure doesn't support to return values with different data types. In your scenario, you could convert [Date] to Text with below DAX formula.

Measure =
IF (
    SELECTEDVALUE ( TableName[Date] ) = DATE ( 9999, 12, 31 ),
    "END",
    FORMAT ( TableName[Date], "" )
)

Alternatively, you could replace "END" with blank.

Measure =
IF (
    SELECTEDVALUE ( TableName[Date] ) = DATE ( 9999, 12, 31 ),
   BLANK(),
    SELECTEDVALUE(TableName[Date])
)

Best regards,

Yuliana Gu