IF Statement evaluating dates is probably working great, but not like I think it should

[pmdave]

Here is some low-hanging fruit for someone...but would be a great help to end my frustration.

Pulling data in from an Oracle Database to Power BI.  I need to ignore a bug in our Oracle Application that will at times write in a date of 01/01/4019 in the data.  Went to Oracle to get this fixed and they told me they have it filtered out from the UI of the application, so I could go kick rocks, which is another reason why Oracle sucks.  Moving on, the Field format in Power BI is mm/dd/yyyy if that matters.  When that value appears in the Oracle data, I don't want it to appear in our reports in Power BI.  So, I created a new column and turned to my simple IF statement that will substitute null for any values of January 1, 4019 that may come in, and will write in the date correctly if it is in the next 1,996 years:

RevisedDate = IF('Activity Updates'[Date] = 01/01/4019, BLANK(), 'Activity Updates'[Date])

It in essence returns a replica of the Date column, and doesn't seem to recognize 1/1/4019 as a date value no matter what format I enter the date in the IF statement.  Until they add the "read my mind please" functionality to Power BI, does anyone know what I have done wrong and why all resulting 1/1/4019 dates are still appearing in my handy "RevisedDate" column still rather than nulls?  Thanks in advance!

[ERD · Accepted solution]

Hi @pmdave , here you are:

RevisedDate =
IF (
    'Activity Updates'[Date] = DATE ( 4019, 01, 01 ),
    BLANK (),
    'Activity Updates'[Date]
)

[pmdave]

ERD,

Thank you so much!  That fixed it!  I had tried using the DATE and DATESBETWEEN statements, but I know my syntax was much different trying to get it to work.  Much appreciated!!!

[Marianna]

Your date should be in quotation marks - try it out and let me know if it works 🙂

[pmdave]

Thank you for your response Marianna!

This is one of the reasons I am confused.  If I put single quotes around the date, Power BI thinks it is a table (understandable) and gives the error "Cannot find table '01/01/4019' ".

If I put the 01/01/4019 in double quotes, it sees it as Text, and errors saying that it cannot compare text to date fields.

I did that, and have also tried different date formats (01-Jan-4019, 01-01-4019, etc.), and those don't seem to work.  Feels like this is such a fundamental problem that I am missing...it's a simple IF statement...but I'm stumped.  Again, thank you for your response!