IF formula for Custom Column error

[VincentCeul]

My goal is to replace empty date values in a table to a custom date value (31/12/9999 e.g.) by adding a new column where I use an IF function.

The new column should be composed out of '31/12/9999' is the value in column X is blank, while if the value in column X is not blank, the column should take the value that is in column X.

The custom column formula I use is IF ( ISBLANK ([Date Finished]) , ("31/12/9999"), [Date Finished]), but I receive the error: Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.

[amitchandak]

@VincentCeul , Try like

IF ( ISBLANK ([Date Finished]) , date(9999,12,31), [Date Finished])

[VincentCeul]

Dear, I still receive the same error. If something wrong with my settings?

[manikumar34]

@VincentCeul 

IF ( ISBLANK ([Date Finished]) , DATE(9999,12,31), [Date Finished])

https://docs.microsoft.com/en-us/dax/date-function-dax

Refre the above link to learn about DATE function

Regards, 

Manikumar

[VincentCeul]

I don not see where this helps my problem? 🙂

[VincentCeul]

Any more ideas?  @amitchandak 

[amitchandak]

@VincentCeul , That was DAX.

Try this on Power Query

if [Date Finished] =null then Date.FromText("9999-12-31") else [Date Finished]

[manikumar34]

@VincentCeul , 

You are trying DAX in Power query. 

Please use the same DAX with Calculated column. 

Regards, 

Manikumar