Short Date Format(01 Jan 2018)

[rkumar]

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

[v-yuta-msft · Accepted solution]

Hi rkumar,

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

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

Regards,

Jimmy Tao

[vanessafvg]

@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")

[rkumar]

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.

[vanessafvg]

@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?

[rkumar]

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.

[vanessafvg]

@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

[rkumar]

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 "

[vanessafvg]

@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?