Power BI report converting UK date to US Automatically

[Anonymous]

I am trying to produce a report that will list an individuals references for a person from earliest to latest. If an individual is still at their current position then the "End Date" field is blank. To make it more user friendly I want this to say "To Current" if the field is blank. 

I do this by converting the "End Date" field to text format and then calculating the following column:

NewEnd = IF(ISBLANK([End Date]), "To Current", [End Date])

This works in Desktop and gives me the following results:

Employer	Start Date	New End
A Bank	01/01/2020	To Current
A University	01/01/2019	31/12/2019

However when I publish to Service it reformats the date to US format as such:

Employer	Start Date	New End
A Bank	01/01/2020	To Current
A University	01/01/2019	12/31/2019

I do not know why when converting "End Date" to Text it reformats the date on Service but not on Desktop (and my browser settings are for English(UK)).

If anyone has any advice it would be greatly appreciated!

[v-lili6-msft]

hi  @Anonymous 

In power bi service, this depends on your browser language Settings.

just refer to this blog:

https://nform.com.au/how-to-change-the-date-format-in-the-power-bi-service/

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Unfortunately, my browser language is English(United Kingdom) and on Power BI it is set to default browser language.

Another thing is if this was the case then it would affect the start date as well, whereas currently start date is not affected at all and only the ambiguous dates in end date are.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

If so, you could also try to adjust the formula as below:

NewEnd = IF(ISBLANK([End Date]),  "To Current",FORMAT([End Date],"dd/mm/yyyy"))

Result:

Regards,

Lin

[Anonymous]

That seems to have fixed the issue thank you! I didn't know you could convert a Date type into a Text type in an if statement like that.

[ibarrau]

Hi. The date will depend on the browser by default. You may be able to change this for a workspace. If you go to schedule refresh and then to "General" tab (I don't remember other way to get here). You will find this menu. Change the default if you want it to be the same for all users.

language settings

Regards,

[Anonymous]

Unfortunately this does not solve the issue and has already been tried.

One of the weirder parts is if the date is something like 24/05/2017 for example it knows it must be of the format DD/MM/YYYY as otherwise it would be invalid. However if the date is of an ambiguous format such as 06/05/2005 it will switch the format to MM/DD/YYYY from DD/MM/YYYY, meaning that there are two different date formats in the same column.

I have managed to create a work around to the problem now but really just want to try and understand why when publishing from Desktop to Service this inconsistent change of date format happens.