cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Short Date Format(01 Jan 2018)

Hi All,
We need to convert date from any format to dd mmm yyyy format eg '2018-03-21' to '21 Mar 2018' and sort it. 
Is is possible to do that .

 

Thank You

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi rkumar,

 

To change format, click Modeling->Format->14 March, 2001(d MMMM,yyyy).

10.PNG

To sort, right click on the column then click “Sort Ascending” or “Sort Descending”.

11.PNG

 

Regards,

Jimmy Tao

View solution in original post

8 REPLIES 8
Highlighted
Super User I
Super User I

@rkumar where are you trying to sort it and in what?  most visuals allow you to sort

 

as for the format

 

format(datecolumn, "d MMM yyyy")

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

We are trying this in waterfall chart.

We have a date column with date format as '01 March 2017' . When we use this in Category field, it is showig fine in x axis . Data is showing fine and we are able to sort also.
But the requirement is to show date as '01 Mar 2017' now when we make the required changes in PowerBI or database to achieve this this, the new column datatype changes from date to text and then the sorting fails.


Highlighted

@rkumar then use a numerical date to sort the text date by as, i.e

 

ie under modelling tab use sort by column and use a numerical expression of the date to sort by

 

does that make sense?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Hi Vanessafvg,
Thanks for the reply .
Yes I already tried same but it gives error :
We cant sort the 'shortdate' column by 'calendar_date'. There cant be more than one value in 'calendar_date' for the same value in 'shortdate'. Please choose a different column for sorting or update the date in 'calendar_date'

This is because more than one records entered on the same date.

1.PNG

Highlighted

@rkumar you need a date table, because you have duplicates, a date table will remove the duplication, create a date table and create  relationship between the two tables on the date then use a numerical date to sort on from the date table and use the short date from the date table





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

The date_id column in the above screenshot is from date table (W_Calendar_D) and this tabe has all distinct dates. The duplicates are in Product table. somehow while sorting its not removing data (As the message says, There cant be more than one value in Date_id for the same value in ShortMonthDate .) 

"Do we need distinct values in both tables "

Highlighted

@rkumar what i am saying is you should be using your date table values not the other table to do the sort by

 

effectively you should have a numerical date in your transaction table, that should link  (relationship) to a numerical date in your date table in your date table you should have the short date time, you then sort the short date time by the the date in the date table.  The short date time should not be in the transaction table. not if you want to use it in this context, make sense?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

Hi rkumar,

 

To change format, click Modeling->Format->14 March, 2001(d MMMM,yyyy).

10.PNG

To sort, right click on the column then click “Sort Ascending” or “Sort Descending”.

11.PNG

 

Regards,

Jimmy Tao

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors