Forum Discussion

Dan27's avatar
Dan27
Regular Visitor
8 years ago

Comparing two dates when one field is text and the other a date

Hello   I'm hoping someone can help. In my data I've got two different fields. An opened date and a closed date. My opened date always contains an date so the format is date. The closed date doesn'...
Need Help
v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
8 years ago

For the NUll, we have a function IsBlank() could be used.

You could first check if the closed date is blank first.

The formula should be changed as below:

IF(Or(!(opened_date)=(closed_date), IsBlank(closed_date)),0,1)

Regards,

Michael

  • Dan27's avatar
    Dan27
    Regular Visitor
    8 years ago

    Thanks Michael, however when I try this I still get the error below

     

    The syntax for '!' is incorrect. (DAX(IF(Or(!(opened_date)=(closed_date), IsBlank(closed_date)),0,1))).

    • v-micsh-msft's avatar
      v-micsh-msft
      Microsoft Employee
      8 years ago

      My bad.

       

      Try the following:

      IF(Or(not((opened_date)=(closed_date)), IsBlank(closed_date)),0,1)

      Regards,

      Michael

      • Dan27's avatar
        Dan27
        Regular Visitor
        8 years ago

        Thanks Michael. That cleared that error but I still get the following:

         

        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.