Forum Discussion

colettb's avatar
colettb
Helper I
10 months ago
Solved

Formula Error Using Date Variables - DAX error does not make sense to me

Hello all,

 

I am trying to create a flag of 1 or 0 based on the timing of 2 date columns in my table, but I am getting the error message "DAX comparison does not support comparing values  of type Date with values of type Text".  Both of my date variables are stored in power BI as type Date, so I'm unsure where my error is?  

 

Here is my formula:

Flag = IF(ISBLANK('Metrics'[Date1]) & 'Metrics'[Date2]<(today()-60),1,0)
 
What I'm trying to accomplish is if Date1 is not null and Date2 is less than today-60 days, then the flag is 1, otherwise 0.
 
Any help is really appreciated!
Need Help
  • Cookistador's avatar
    Cookistador
    10 months ago

    hi colettb 

     

    The error happens because a single ampersand (&) is used for joining text strings (concatenation). Power BI is trying to concatenate a string with a date comparison, causing a type conflict.

    To fix this, you need to make two changes:

    1. Use the double ampersand (&&), which is the logical AND operator for combining conditions.
    2. To check if the date is not null, wrap your ISBLANK() check in the NOT() function.

     

    Flag =
    IF(
    NOT(ISBLANK('Metrics'[Date1])) && 'Metrics'[Date2] < (TODAY() - 60),
    1,
    0
    )

2 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    10 months ago

    hi colettb 

     

    The error happens because a single ampersand (&) is used for joining text strings (concatenation). Power BI is trying to concatenate a string with a date comparison, causing a type conflict.

    To fix this, you need to make two changes:

    1. Use the double ampersand (&&), which is the logical AND operator for combining conditions.
    2. To check if the date is not null, wrap your ISBLANK() check in the NOT() function.

     

    Flag =
    IF(
    NOT(ISBLANK('Metrics'[Date1])) && 'Metrics'[Date2] < (TODAY() - 60),
    1,
    0
    )