CODEWITHSUNDEEP
powerbidax
swordHeart
swordHeart

Reputation: 51

PowerBi DAX write DAX to compare date time

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.

Upvotes: 1

Views: 26859

Answers (3)

Nitin Singh
Nitin Singh

Reputation: 11

The problem in your code is, it is comparing date with text data type, you need to pass matching value in date format, for which you can use the following function

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

or 

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

Upvotes: 1

sergiom
sergiom

Reputation: 4887

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
)

Upvotes: 2

swordHeart
swordHeart

Reputation: 51

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)

Upvotes: 0

Related Questions