Date Formats - MM/DD/YYYY to DD/MM/YYYY

[Anonymous]

Hi,

I am creating a report for my colleagues in Australia & New Zealand, who view dates as DD/MM/YYYY instead of the American format of DD/MM/YYYY.  Can someone please explain the best way to do this?

Thanks,

Gary

[suryadeepa]

Hi,

Goto Model View -> select the field in the table -> In Properties tab Change the datetype to "Date" ->Date Format "Custom" ->Custom format "Short Date" -> "mm/dd/yyyy.

[v-juanli-msft]

Hi @Anonymous 

For this problem, there are many workarounds:

https://community.powerbi.com/t5/Service/How-to-Dispay-Date-columns-in-DD-MM-YYYY-format/td-p/191760

https://community.powerbi.com/t5/Service/Date-issue-dd-mm-yyyy-vs-mm-dd-yyyy/td-p/560290

Best Regards
Maggie

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

[amitchandak]

Sometimes the formatting will not workout. Create a new formula column. Use left, right and mid function and make it as mm/dd/yyyy date and mark that column as date as well as sort column for the dd/mm/yyyy column.

mid(DD__MM__YY[Date],4,2) &"/"& left(DD__MM__YY[Date],2) & "/" & right(DD__MM__YY[Date],4)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[lukasz12345]

Unfortunately, this not works. 

Date is created like this: 

DD/mm/YYYY = day(DateTimeDim[DMY]) &"/"& month(DateTimeDim[DMY]) & "/" & DateTimeDim[Year]

Despite used data type and format (date is mandatory here because of slicer visual) format is always taken from windows regional setting instead of data parameters in power BI (Should be DD/MM/YYYY instead of MM/DD/YYYY)

[Anonymous]

Give a try as this:

Eric Ji | Senior Business Intelligence Consultant
www.designmind.com

[Anonymous]

Hi @Anonymous , as I mentioned to @Tahreem24, this will not work as I do not have DD/MM/YYYY as an available date format in the Modelling tab.

[alena2k · Accepted solution]

Hi! Try again with date format, but make sure that you set Data type: to Date first (vs Date/Time). It will open different set of options under Format:

[VKrishna]

My data type is Date, why am i seeing these option in Format instead of dates. pls help.

[VKrishna]

Thanks a lot @alena2k. That helped. 

[alena2k]

hi @VKrishna you are in Query Editor, you need to load your data to the model (Home > Close & Apply) and you will see different Format options in Power BI Desktop. I hope it helps!

[Anonymous]

Hi

You need to change your date field from "text" (string) to Date Type. Once this is done you can use format to change to whatever format you want.

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[Anonymous]

Hi @Anonymous, thanks for the reply but the format is already set to Date, so this is not the solution.

[Anonymous]

Hi Gary,

I think I understand your issues now:

You need your a US Date Date Type and another Date of Date Type with a different Locale.

See this blog which I believe can you show you what you need. 

https://www.oraylis.de/blog/local-date-formats-in-power-bi

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[mahenkj2]

The link you indicated is really helpful and can save extracting the dd, mm and yy pcs of text.

[Tahreem24]

Hi,

Approach 1: use the Format function to show date in the required format. FORMAT(Table[Date Column], "DD/MM/YYYY").

Approach 2: select your date field then go to modelling tab then change the data format option according to DD/MM/YYYY.

Please give Kudos to this effort and mark it as a solution if it helps you!!!

[Anonymous]

@Tahreem24 thank you for the response, however I tried Approach 1 and this did not work, as it turned my fields into a text format.  Approach 2 won't work either since DD/MM/YYYY is not an available option for me in the Modelling tab.