Date issue - dd/mm/yyyy vs mm/dd/yyyy

[OB]

Hello,

My report date is set to dd/mm/yyyy in Power BI Desktop. Regional Setting is Engligh (Canada). Once I publish it to Power BI Service, it's displayed in the US format mm/dd/yyyy in the classic slicer visualization, while it's displayed the same way as in the Power BI Desktop when I use the Smart Filter by OKViz.

Also, the card visual where I use "if ISFILTERED on Date condition, then display the date", defaults the output to the US Date format even in Power BI Desktop, so I have to use FORMAT function in DAX to force it displayed in dd/mm/yyyy. However, if I display the date by itself or the max/min date in the report, it uses dd/mm/yyyy in Power BI Desktop.

My report uses a direct query from a SQL database and the date pulled from there is displayed in the Query Editor as dd/mm/yyyy.

My Capacity Settings are Canada Central. The language is set to English. My Windows settings have short date dd/mm/yyyy settings and long date d-mon-yy setting from what's available. I tried Chrome and IE and they display the same thing.  I tested to set Chrome to English (Canada), but it doesn't help either.

I can use a workaround, but I believe this is an issue, which shouldn't happen. I saw similar issues reported by:

- New Zealand here https://community.powerbi.com/t5/Issues/Power-BI-Service-Dates-dd-mm-yyyy-and-mm-dd-yyyy/idi-p/54038... and
- Australia here https://community.powerbi.com/t5/Service/How-to-change-date-format-from-MM-DD-YYYY-to-DD-MM-YYYY-in-... and
- Norway/UK here: https://community.powerbi.com/t5/Service/Date-formatting-issues-MM-DD-YYYY-gt-DD-MM-YYYY/m-p/535891#...,

so I hope this can be fixed for non-US Power BI users.

Thank you

[OB]

@Anonymous, thank you for your support!

@v-yuezhe-msft, this is exactly what I was looking for!  Thank you for spelling it out for me.  Once I set the Power BI Service Settings to Default (browser language) everything fell into place. It works both in Chrome and in IE because I have the browser language set to English (Canada) in both cases.  Earlier when I was testing, I was switching between English and French language in Power BI Service Settings. The French did the job for me, while English forced dates to mm/dd/yyyy format. 

Mystery solved 🙂

[v-yuezhe-msft]

@OB,

Please re-check my previous reply.

Please make sure you set language settings as Default(browser language) in Power BI Service, and ensure that you set English (Canada) as default language in Chrome, and move it to Top. If you still get issues, please post screenshots about Power BI Service language setting and chrome language setting

Regards,
Lydia

[Anonymous]

Have you tried to cchange the language to English (UK)? That's what I have and will have the correct format for you?

[OB]

Lucky you 🙂

As I was testing, I created a copy of the file with a different name and published it to a different workspace too and nothing has changed until I set Power BI Service to French language.  That's when my dates got the expected look dd/mm/yyyy.

Thank you for sharing!  Maybe it'll help others, while I'll be waiting for a fix.

[Anonymous]

Hi,

I had the same issue and found a fix while on the phone to someone from Power BI!

It was an issue with the file we uploaded - so re-save your desktop file with a different name and reupload into your workspace. It's worked for me, hopefully it works for you too.

Does seem like a bug that needs fixing though....

Good luck!

[OB]

Hello,

The solution above doesn't work and that's exactly what I described in my original post.  I did all the changes before posting the issue because I wanted to exhaust all options before asking for help.

To confirm that the issue is with Power BI Service, I took time today and did the following:

- validated that the region, language and date format on my computer are set to English Canada

- validated that the Chrome language is set to English Canada

- validated that the Power BI Service language was set to English (there's no option for English Canada, it's simply English there).

This didn't help and the date was still displayed mm/dd/yyyy in the Power BI Service.

After that I changed the language in Power BI Service to French and all the dates in the reports were beautifully displayed in dd/mm/yyyy format.

I can't use the workaround suggested by others to convert the date column into text because I'm using the date in a standard Power BI slicer and I need to display a date range, which I don't find in any other visual.  If If the input field for the date slicer has type text, the only way to see dates is a list and it's really cumbersome for the user to be clicking tons of dates if they want to analyze a wide date range.

I also can't use the FORMAT function in DAX because it's not compatible with Direct Query.

Please pass it on to the Development team to investigate.  I can send snapshots for all my steps if needed.

Thank you

[Anonymous]

I'm having the same issue! I've got my browser in the correct language and it hasn't helped.

What's the fix?!

[v-yuezhe-msft · Accepted solution]

@OB,

Firstly, please change the region settings on your computer as below. This way, when you use ISFILTERED, the output date can display in DD/MM/YYYY format.


Secondly, after you publish the PBIX file to Power BI Service, the date format will change based on the language settings in Power BI Service. Please make sure you set language settings as Default(browser language), also ensure that you set English (Canada) as default language in Chrome, and move it to Top.
Power BI Service settingPower BI Service visualChrome setting

Regards,
Lydia