Sorting custom date fields

[rkumar]

Hi All,
Thankyou for the help.

While using line chart we are using trimmed down date format '14 March' instead of '14 March 2017' , due to this the x axis is not being sorted correctly (As dates should be).

Tried sorting the column on basis of date_id but it gives error message.

: 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'

Is there a workaround or some other way to accomplish the same.

[Anonymous]

Hi,

One of the workarounds-

Create a custom column which consists of date and month number concatenated.

E.g.: For March 04, the custom column value will be 0304 - First is the month number (03), followed by date/day (04).

Similarly, for April 30 the value will be 0430.

Use this custom column in 'sort by column' option.

This should help out.

Regards,

Suguna. 

[rkumar]

Hi Suguna,

Same result as mentioned in my first post. 
nyways, Thankyou for the help.

[ElliotP]

The best way I've found to solve this problem is the example given above.

In the Query Editor you can use under the 'Add Column' tab "Create Column from Example" or go another route to create a concated numeric column which is monthyear(011994 for example), apply the query and then back in edit mode of Power BI you can sort your current trouble column by the sorting column you have just created.

If that doesn't work, maybe try extracting a new date column in query editor from your current overall DateKey and use that as it will be sorted.

[Anonymous]

@rkumar,

Please check my reply in the following similar thread.

https://community.powerbi.com/t5/Desktop/Sorting-Error/td-p/243161

Regards,
Lydia

[rkumar]

Hi Lydia,
Thankyou for the help.
But in our case we cannot change data to match the dates.