Date goes from dd/mm/yyy to mm/dd/yyyy when used in function

[123CaM321]

So I have a date column in the format dd/mm/yyyy and need to use it to find yesterdays date. I use

yesterday = [date] - 1

20/01/2023 becomes 01/19/2023

I have set it to use locale on the original inside power query, that is how [date] is already in the right format.

Got me stumped

[Idrissshatila]

Hello @123CaM321 ,

Try This 

Yesterday = format( [date] - 1 ,"DD/MM/YYYY" )

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[123CaM321]

Thanks. Sorry I have tried tihs but because I later use "yesterday" to lookup a value in another table being of type DATE, I receive an error that says

"Function 'LOOKUPVALUE' does not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

Maybe I could look to change the data type inside the table I'm using LOOKUP on... I'll try this

[123CaM321]

Unfortunately I think I need to keep it as a DATE format

[Ashish_Mathur]

That format switch should not have happened in the first place.  Nevertheless, you can still use that column (yesterday) for your calculations.