How to Dispay Date columns in DD/MM/YYYY format

[Surendra_thota]

HI All

we want to display the Date columns in UK format , we have changed the browser language to En-UK , still we are not able to refelect the format , we have tried with Format function , the problem is it is treating as string  when we are sorting the records based on that formatted date column . please let us know how to achieve this

[v-ljerr-msft · Accepted solution]

Hi @Surendra_thota,

You should be able to follow steps below to display date column in DD/MM/YYYY format, and sort it correctly. 

1. Use FORMAT function to create a new calculate column in your table as you have done before.

Column = FORMAT(Table1[Date],"DD/MM/YYYY")

2. Select the new calculate column in Data view, then click "Sort by Column" under Modeling tab, and choose your original date column instead of the default column.

Regards

[tjd]

I have several international clients.  To avoid confusion amongst them (Americas vs Europe vs Asia), I always use the 01-Mar-2017 format for all my dates.  You set it in the data view under the Formatting tab on the Modeling ribbon.

[Anonymous]

Hi @tjd,,

I'm trying to format as dd/mmm/yyyy to avoid the confusion..... just as you say. But I cannot find that particular format option under the dates drop down list, and I cannot find a way to apply a custom format?

I've set my column to Data Type Date and schrolled through the many different options, but there is not the one mentioned?

How do you do it?

Thanks

Rob

[tjd]

It's found in PowerBI Desktop, not in the Power Query editor:

It's availability might be dependent upon your Regional Settings.  I'm using English (US).

[Anonymous]

hi @tjd,

Thanks for your reply.

I've tried changing the Regional Settings on the PBI Desktop File/Options dialog. I am in the UK so I was using English (UK), I've changed to English (US) and I still don't see the same date format options as you do. I get just about every combination other than dd-mmm-yy !

I wonder if its the PowerBI desktop versions that are different? I'm using the most recent Report Server version of the desktop app August 2018. I deploy on prem. Are you using the Azure version? Newer version maybe than mine?

Thanks

Rob

[tjd]

Rob -

I'm using the regular Windows PowerBI Desktop (not Report Server), and I'll bet you that is the difference.  My version is the most recent (October 2018).  Perhaps a moderator here could help you out.  Sorry.

[Anonymous]

@tjd

Thanks Anyway