Dax switching days and months

[campelliann]

Hi,

So this is what happened to me yesterday. I was trying to use the function Format(date, "yyyy-mm-dd"), and then dax was placing me the day in the middle (instead of the month).

For instance the date field was like 11-02-2021 11:30:00, and then I would get, 2021-11-02 after the format. The regional settings are set in my country..

Any explanation for this?

Thank you!

[v-alq-msft · Accepted solution]

Hi, @campelliann 

Based on your description, I assume that the date format in your country is dd-mm-yyyy. You may go to 'Query Editor' and follow the steps.

This locale setting is important for interpreting text values into a specific data type. For example, imagine that you have your locale set as English (United States), but a column in one of your CSV files has dates formatted in the United Kingdom format of day/month/year.

When you try setting the data type of the Date column to be Date, you get error values.

These errors occur because the locale being used is trying to interpret the date in the English (United States) format, which is month/day/year. Because there's no month 22 in the calendar, it causes an error.

Instead of trying to just select the Date data type, you can right-click the column heading, select Change type, and then select Using locale.

In the Change column type with locale dialog box, you select the data type that you want to set, but you also select which locale to use, which in this case needs to be English (United Kingdom).

Then you may use dax function Format(date, "yyyy-mm-dd") to format the date column. For further information, please refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaideepnema]

Hi @campelliann  did you checked the regional settings in Power Bi Desktop ?

If this answers your question please accept this as a solution !!

[campelliann]

Hi, yes! The regional settings are in my country. It was very strange...