PowerBi DAX write DAX to compare date time

0
votes

I want to set a column value before 2021-1-20 as 0. So I need to compare date time in data. Here is my wrong code.

ColumnValue = IF([MyDate]>="1/20/2021",
SWITCH([Key],
"caseA",[Value],
"caseB",[Value],
"caseC",[Value],
"caseD",-1*[Value],
"caseE",-1*[Value],
0
),0)

However, it hints that DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values. But I don't know how to compare DAX date time.

2
powerbidax

2 Answers

0
votes

I already know the answer, by using DATEDIFF

ColumnValue = IF(DATEDIFF(DATE(2021,1,20),[MyDate],DAY)>=0,
SWITCH([Key],
"caseA",[Value],
"caseB",[Value],
"caseC",[Value],
"caseD",-1*[Value],
"caseE",-1*[Value],
0
),0)
0
votes

you can write the date as a text using the yyyy-mm-dd format and then use VALUE like follows

ColumnValue =
IF(
    [MyDate] >= VALUE( "2021-01-20" ),
    SWITCH(
        [Key],
        "caseA", [Value],
        "caseB", [Value],
        "caseC", [Value],
        "caseD", -1 * [Value],
        "caseE", -1 * [Value],
        0
    ),
    0
)