cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PR20048119 Frequent Visitor
Frequent Visitor

how to compare date column in measure?

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: how to compare date column in measure?

Hi @PR20048119 ,

 

"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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: how to compare date column in measure?

Hi @PR20048119 ,

 

"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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.