cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mmahmood Frequent Visitor
Frequent Visitor

FORMAT changes date ignores locale (bug or feature)

Hi All,

 

Here is my data (NB the FORMAT function is correctly formatting the date in the column next to it):2018-06-21_09-59-51.png

 

 

 

 

 

 

 

 

 

 

However changing the data type on the modelling tab gives this:2018-06-21_10-03-22.png

 

 

 

 

 

 

 

 

 

 

Funnily enough if I change the order in the FORMAT, it gets it right...!

2018-06-21_10-04-45.png

 

Any ideas on how to force it to use UK date formatting?

 

Thank you.

 

PS I have my options and settings to “Automatically detect column types and headers for unstructured sources” and my Regional Settings are set to English (United Kingdom).  The Date column in question has been also transformed to date format using the locale in the Query Editor.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: FORMAT changes date ignores locale (bug or feature)

Hi @mmahmood,

 

Where did you download  your Power BI Desktop? Direct download or Windows app store?

 

The DAX format function follows the locale of the model. There are 2 ways this can be controlled, depending how the user installs PowerBI Desktop.

1. If they are getting it from Windows Store, PBI Desktop will have an additional set of Regional Settings, one of which is "Model Language". This allows the user to chose the specific language they want DAX to follow. This only applies for new models, existing ones cannot be changed.

 

2. If the user is getting the installer from the PowerBI web page, that installation has just one language available and there is no setting for changing the model language. All the models created with that installation will have the installation language set by default. For example if they download the English PBI Desktop all models created will be in English, if the download the Dutch PBI Desktop, all the models created will be in Dutch.

 

I have had many tests on Power BI desktop getting from Windows App Store, it seem that works as expected.

 

date format.PNG

 

 

In addition, please check if your regional  has been setted to be UK when you import data to Power BI Desktop. 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team
Community Support Team

Re: FORMAT changes date ignores locale (bug or feature)

Hi @mmahmood,

 

Then locale only have affect on the data type when you import. It will detect the data type by the format you data.

 

By my test, for your issue, it may caused by the time setting of your system. 

 

I could reproduce your issue when my system time setting is 6/22/2018.

 

When I change my region and the time format of my system, the format result is correct.

 

Here is my system time format.

time.PNG

 

 

Capture.PNG

 

You could see the details for the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mmahmood Frequent Visitor
Frequent Visitor

Re: FORMAT changes date ignores locale (bug or feature)

Thank you for your reply.

 

My regional settings are in the dd/mm/yyyy format:2018-06-22_08-59-28.png

 

 

 

I have attached a GIF of the change instead with my clock in the corner Smiley Happy

2018-06-22_09-05-41.gif

 

 

 

Community Support Team
Community Support Team

Re: FORMAT changes date ignores locale (bug or feature)

Hi  @mmahmood,

 

That's really strange. I have made a test again that I could get the format of dd-mm-yyyy.

 

date uk.PNG

 

You could have a test on my test file when your system regional setting is UK format.

 

If you still have this issue, cpuld you share your dummy pbix file which can reproduce the issue, so that I could have a test on my side? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mmahmood Frequent Visitor
Frequent Visitor

Re: FORMAT changes date ignores locale (bug or feature)

Thanks for your help Cherry.

 

I have downloaded your file and as you can see from the attached GIF, it doesn't seem to be specific to the pbix. 

2018-06-27_10-52-30.gif

 

 

I have re-attached the file to see what happens if you played with it:

 

https://www.dropbox.com/s/j5mugbbfypgoyrg/UK%20date%20%281%29.pbix?dl=0

 

As far as I know all of my Windows, Office and application settings are set to UK.

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: FORMAT changes date ignores locale (bug or feature)

Hi @mmahmood,

 

Where did you download  your Power BI Desktop? Direct download or Windows app store?

 

The DAX format function follows the locale of the model. There are 2 ways this can be controlled, depending how the user installs PowerBI Desktop.

1. If they are getting it from Windows Store, PBI Desktop will have an additional set of Regional Settings, one of which is "Model Language". This allows the user to chose the specific language they want DAX to follow. This only applies for new models, existing ones cannot be changed.

 

2. If the user is getting the installer from the PowerBI web page, that installation has just one language available and there is no setting for changing the model language. All the models created with that installation will have the installation language set by default. For example if they download the English PBI Desktop all models created will be in English, if the download the Dutch PBI Desktop, all the models created will be in Dutch.

 

I have had many tests on Power BI desktop getting from Windows App Store, it seem that works as expected.

 

date format.PNG

 

 

In addition, please check if your regional  has been setted to be UK when you import data to Power BI Desktop. 

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mmahmood Frequent Visitor
Frequent Visitor

Re: FORMAT changes date ignores locale (bug or feature)

I did indeed download it from the website.

 

I uninstalled it and re-installed from the Windows Store.

 

Created a new model (you're absolutely right, the old pbix files kept the US model) and tried formatting it again.

 

This time it was correct. 

 

All is good in the world once more Smiley Very Happy

 

What on earth are all these emojis?  Cat Happy Cat Indifferent Cat Mad What is this? Cat Sad Cat Surprised Cat Very Happy  Pikachu?

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 1,006 guests
Please welcome our newest community members: