topic Re: IF Statement evaluating dates is probably working great, but not like I think it should in DAX Commands and Tips

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

Anonymous — Thu, 31 Aug 2023 20:55:24 GMT

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!

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

Marianna — Fri, 01 Sep 2023 05:28:04 GMT

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

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

Anonymous — Fri, 01 Sep 2023 12:59:22 GMT

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!

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

ERD — Fri, 01 Sep 2023 15:37:44 GMT

Hi Anonymous , here you are:

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

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

Anonymous — Fri, 01 Sep 2023 15:49:26 GMT

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!!!