Date format dd/mm/yyyy works on most, but not all rows in my data table

[Aukje88]

Hi everyone,

I have a column with a date formatted as DD/MM/YYYY.

It is called "Start of Week" and it has the date of the first Monday in the week based on another column called "Date".

The format DD/MM/YYYY is applied correctly to all of the dates, EXCEPT for a few dates in May.

So 2 May 2022 is displayed as 05/02/2022.

And 9 May 2022 is displayed as 05/09-2022.

But alle the other dates are displayed correctly.

How can this be? Does anyone know if this is a bug, or if I can do anything to fix it?

 

Thanks in advance, Aukje

[rajendraongole1]

Hi @Aukje88 - To fix this Set the Regional Settings to a locale that uses DD/MM/YYYY (for example, the USA).

Go to files>> options and settings >> options >> Current file >> Regional settings 

In the table view, select your "Start of Week" column and go to the Modeling tab, then select Format > Custom. Enter the custom format DD/MM/YYYY.

you can also check in power query editor on date column as below:

Hope this helps to change and maintain consistancy . still exist share file by removing the sensitive data for us.

[Aukje88 · Accepted solution]

Hi Rajendraongole1,

Thanks for thinking along. My settings were (almost) exactly like you described.

My Windows Regional format settings are "English (Netherlands".

My Power BI Current File Regional settings are "English (USA)".

And I have used a custom format DD/MM/YYYY for the column "Start of Week".

"Start of Week" is a column that is created in the Query Editor from the column "Date".

In the Query Editor, all the dates were formatted correctly.

In Power BI, ALMOST all dates were formatted correctly. So it all seemed to work fine, EXCEPT for a few dates in May.

In creating more examples, I have actually stumbled upon a fix:

Went to Query Editor, and filtered the "Date" column to ony a few dates in May.

Closed and Loaded the data. This fixed the issue.

Then went back and cleared the filter from the "Date" column in the Query Editor, and loaded the data again.

Thanks for getting involved!

Aukje

[HotChilli]

Probably not a bug.

What is "Start of Week" - DAX calculated column? or Power Query custom column? or are you importing with the data source?