cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rkumar Regular Visitor
Regular Visitor

Sorting custom date fields

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.

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Sorting custom date fields

@rkumar,

Please check my reply in the following similar thread.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rkumar Regular Visitor
Regular Visitor

Re: Sorting custom date fields

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

bidevsugmen Regular Visitor
Regular Visitor

Re: Sorting custom date fields

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 Regular Visitor
Regular Visitor

Re: Sorting custom date fields

Hi Suguna,

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

ElliotP Member
Member

Re: Sorting custom date fields

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.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 349 members 3,268 guests
Please welcome our newest community members: