FORMAT changes date ignores locale (bug or feature)

[Anonymous]

Hi All,

Here is my data (NB the FORMAT function is correctly formatting the date in the column next to it):

However changing the data type on the modelling tab gives this:

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

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.

[v-piga-msft]

Hi @Anonymous,

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.

You could see the details for the attachment.

Best Regards,

Cherry

[Anonymous]

Thank you for your reply.

My regional settings are in the dd/mm/yyyy format:

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

[v-piga-msft]

Hi  @Anonymous,

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

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

[Anonymous]

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. 

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.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

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.

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

Best Regards,

Cherry

[Anonymous]

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 

What on earth are all these emojis?  What is this?     Pikachu?