DATE/VALUE error with IF & OR statement

[Anonymous]

Hi everyone,

I have an IF & OR statement te syntax is below and I am getting the following error "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."

Syntax

Have students increased in the period KS2 cohort = FORMAT(IF([Date of student enrollment ]=BLANK()  ||  [Date of student enrollment]="NULL" ||  {Date of student enrollment ]="-"   ||  [Date of student enrollment] || [Date of student enrollment]="na","No",
    IF([Date of student enrollment ])>=[Date of student outreach],"Yes","No")),"")

Even with trying to use Format I am getting an error, thank you in advance for your help

Regards,

P

[petrovnikitamai]

i'm not sure but try this

new column = 
IF([Date of student enrollment ]=BLANK()  ||  
    [Date of student enrollment]="NULL" ||  
    [Date of student enrollment ]="-"   ||  
    [Date of student enrollment] || 
    [Date of student enrollment]="na","No",
IF(DATEVALUE([Date of student enrollment ])>=DATEVALUE([Date of student outreach]),"Yes","No"))

[Anonymous]

Unfortunately, that still came up with an error

[PattemManohar]

@Anonymous As per the expression you have posted - I can understand that if "DateOfStudentEnrollment" > "DateOfStudentOutReach" then "Yes" otherwise "No". But there are blank values and other text data types in "DateOfStudentEnrollment" you can use that as Date Comparision with other Date field i.e. "DateOfStudentOutReach". That's the reason for your error. You need make those fields of same datatype (in this case it will be string). 

[Anonymous]

Changing the two columns to text give me incorrect vales when (student enrollment >= date of outreach, "yes", "no" and keeping them both as dates still gives me the Value/format error...:(

[PattemManohar · Accepted solution]

@Anonymous Please try this...

Result = IF(IFERROR(SEARCH("/",Test59StrDate[Enrollment],4),0)>0,IF(VALUE(FORMAT(Test59StrDate[Enrollment],"Short Date"))>=Test59StrDate[Outreach],"Yes","No"),"No")