Date comparison in power bi dax

Question

I had a problem with my code. It returns the below error: DAX comparison operates do not support comparing values of type date with values of type text.

Basically, I want to count rows based on some conditions. And I know there is a need to convert the data type, but I am not sure how to do it.

Total Open Issues = 
--------------------
--basic info
VAR SELECTEDDATE =
    DATEVALUE(SELECTEDVALUE(Calender[FullDateAlternateKey].[Date]))
--------------------
--FIND the relvent data
VAR rlvttable =
    calculatetable(
    Tracker,
    Tracker[Catagory]="ISSUE",
    DATEVALUE(Tracker[ClosedDate])>SELECTEDDATE
       ||Tracker[ClosedDate]=""
    )
--------------------
--Results
Return
    countrows(rlvttable)

Anyone could advise me how to correct it? Thanks~

Olly Olly · Accepted Answer · 2018-12-20T08:38:55

Check the data type of columns Tracker[ClosedDate] and Calender[FullDateAlternateKey] - one of them is Text, rather than Date.

To fix, you could:

choose a different field which is already a Date format
change the format of the offending column
use DATEVALUE in your measure, to convert the text date to a real date.

It also looks like you need to edit this statement, as these conditions conflict:

Tracker[ClosedDate]>SELECTEDDATE
   &&Tracker[ClosedDate]=""

Date comparison in power bi dax

2 Answers