Converting 01/01/1900 date to a blank field

[SandeA]

When a file from a 3rd party was used to create a SQL database, if there was a blank field in one of the date columns, SQL populated the field as 01/01/1900 (my DBA said that is how SQL works). He said he could bring them in as NULLs but I think that would be just as bad.

I'm wanting to create a calculated column where: if the Invoice_Date is 01/01/1900, then put a 0 or blank, otherwise, put the Invoice_date. I've tried using different IF statements with BLANK() and such, but just can't get it right. Is this even possible? 

Thank you in advance!

Sande

[parry2k]

Totally agree with @AlexisOlson . Great point. I would have brought null from the SQL and not worry about adding a new column.

[AlexisOlson]

A NULL in SQL is roughly equivalent to a BLANK() in DAX, so I'm not sure why bringing in NULLs would be "just as bad".

[parry2k · Accepted solution]

@SandeA if you are adding a column using DAX then do the following:

New Date = IF ( Table[Date] = DATE ( 1900, 1, 1 ), BLANK(), Table[Date] )

if you need PQ solution, let me know.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡