PowerBi keeps changing the date format

[Anonymous]

I'm quite sure this is a bug as it began happening after the recent updates the desktop app. All my dates are formatted as the local style (DD/MM/YYYY) and under the column tools I specifically select the same format, see below.

However, all my dates in the visualisations are in M/DD/YYYY format, see below. Despite my selection above.

When I change the format to something else (like D/MMM/YYYY) and change back to what I want, the issue is resolved.

I believe it might be because I have a US keyboard and my Windows is set to US region, but I have different date/time formats that we use locally.

[v-cazheng-msft]

Hi @Anonymous 

I’ve tested in Power BI Desktop(Version: 2.99.862.0 64-bit (November 2021)) based on the description from you, but I didn’t get the same issue. Here are my sample data and they are identified as Text date type at the beginning.

StartDate	EndDate
25/3/2020	31/12/2020
11/11/2021	11/5/2021
25/3/2021	31/12/2020
1/4/2021	28/9/2021
19/9/2017	15/11/2019

I didn’t make any change to them and just apply them in Power Query Editor. After that, I change their date type to date with dd/mm/yyyy format by Column columns. As you can see, they were displayed in dd/mm/yyyy format.

My Local for import and Model language are set as United States.

For how language impacts the data, you may refer choose-the-language-or-locale-of-power-bi-desktop. In addition, I think this article may help you.

If there is anything wrong, please let me know. Also, please check whether you will get this issue in a new pbix file. If this issue keeps happening to you, please let me know the version of your Power BI Desktop and provide a link of a sample pbix file containing this issue. Thanks.

Best Regards,

Community Support Team _ Caiyun

[v-cazheng-msft]

Deliver test result and possible solution.

[Anonymous]

Thanks @v-cazheng-msft  for the input. My regional settings for Global and Current File are different but this has always been the case. The issue only started to appear a few weeks ago, and I thought it was a just a small bug and ignored, but now my users are complaining as they can't be sure if the date is in D/M/Y or M/Y/D format.

 

As recommended, I created a new file and added some random dates. Without making any changes in PowerQuery, as you can see below I have the same issue.

After changing the type using locale (English/Denmark) in PowerQuery, the issue remains the same. As you can see below, if I change the date format to something else, it actually shows the dates as I want them

I'm on version Version: 2.100.684.0 64-bit (December 2021). Attaching the pbix for your information. Here is the simple file in Dropbox, as I don't have permissions to upload pbix files in the forum

[v-cazheng-msft]

Hi @Anonymous 

Thanks for your reply. I've tested with the sample pbix with you. If I set its format as dd/mm/yyyy, it will be displayed as expected. It seems like it works well at my side. 

Could you plese try to change your date format to dd/mm/yy again? If it is wronly displayed in the visual, please remove it from the visual firstly and after that re-add it to the visual. And then, please check whether you will get same issue. Thanks in advance! 

If there is something I misunderstand, please feel free to let me know.

Best Regards,

Community Support Team _ Caiyun

[MiraNi]

Hi,

I have the same type of issue but the problem doesn't occur yet in Desktop but after I publish the report to service. In Desktop dates are formatted as before but in service the result just as the @Anonymous described. 

This report has been created a while ago and the dates have been showing in the same, defined format both in Desktop and Service. But after I made changes to the report (not dates) using October version and published new version, this formatting issue occurred. And this is not only concerning one report but all new versions.

My regional settings are:

 And locale:

[Anonymous]

Hello, @v-cazheng-msft thanks for the response. 

I followed your suggestion, removed the visualizations that had the dates and re-added them but nothing changed.  I have at least 10 different reports with a date field, and in all of them I have the same issue. To clarify, once I change the date format to anything else and change back to DD/MM/YYYY the issue is resolved. But when I save, close and re-open the report, the issue comes back. Similar to @MiraNi all of my reports have been created a long time ago and I've never changed the date format. As mentioned before, the issue started appearing after an update, but I ignored it, thinking it was a minor bug that would have been resolved quickly. Let me know if there is anything else to support the "investigation".

Thanks,

[MiraNi]

Hi,

I found a workaround for this issue. Me and my customer are located in Finland and want to see the local format d.m.yyyy for dates. I have used the date format with asterisk, which should react to the date settings in operative system, which on my computer is d.m.yyyy. Despite the formatting changed in Power BI service for USA formatting so it seems that it doesn't check the operative system setting. When I then changed the browser language from English to Finnish, the dates were back to d.m.yyyy as should. 

Changing the browser language is not the solution. But after I changed the format with asterisk (red) to the format without asterisk (green), the formatting is following from Power BI Desktop to Power BI service as should. This is a workaround only, the root cause and the final solution would be nice to find out.

[matvi]

I have the same problem and it is clearly a bug.

I have this "Year" field formatted this way:

It has been that way for more than a year. Suddently, today, the reports connecting to this dataset stopped using the "yyyy" formatting and instead used the operating system formating.

We have taken the habit of exporting the dataset model to a JSON file and then we put it into git for versionning. So, when we have a strange behavior, we can export the dataset as a JSON file and compare with the one in git to see what changed. 

I did that and I found out that many of the "formatString": "yyyy" attributes has disappeared from the model. 

 In that state, the behavior of Power BI is inconsistent. Even if the format is YYYY in the UI for the field, the field in displayed as YYYY-MM-DD (see picture)

If I change the field setting to something else and then, change it back to YYYY, it works and the JSON file is also impacted (see picture):

I don't want to do this for every field with a format string of every dataset!

Please correct this very annoying bug. 

Thanks, 

Mathieu

[exb]

I am finding that when our users export data to excel, despite the visualisation appearing as expected i.e. dd/mm/yyyy and the date field set to *dd/mm/yyyy it exports and displays mm/dd/yyyy.

I'm quite sure this only happens with datasets developed after I installed the Dec 21 desktop update. I'm now using Feb 22.

When I changed the date format to yyyy/mm/dd, published and exported data it exported as yyyy/mm/dd (as expected).   

When I changed the date format to dd/mm/yyyy (no asterix), published and exported data it exported as mm/dd/yyyy (not expected).

Have checked Windows Regional Settings as per: Supported languages and countries/regions for Power BI - Power BI | Microsoft Docs. All seems right English (Australia). Have tried Chrome and Edge.

Any help would be kindly received.