Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
123CaM321
Frequent Visitor

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

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

4 REPLIES 4
Idrissshatila
Super User
Super User

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

 


Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.