Forum Discussion
Helper VSort months in chronological order
Hi
I have data from January to December. I want to show the data on the graph in a proper sorting (Jan-Feb-...)
I do not want to add a "month no." column and sort it based on that why? because this way, my data will not be sorted on year and could start from 2018-2016-2017...
I need a method to sort the month without need to add a new column.
Thanks
Hi,
Assuming there is a proper date column in your Data Table, create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table. In the Calendar Table, extract Year by using =YEAR(Calendar[Date]). Extract Month by using =FORMAT(Calendar[Date],"mmmm"). Create another 2 column Table with Month names in one column and numbers (from 1 to 12) in another column. Build a relationship from the Month Name column of the Calendar Table to the Month name column of this new 2 column Table. In the Calendar Table, write this calculated column formula =RELATED(Month_order[Order]). Now click on any month cell in the Calendar Table and go to Sort By > New column. To your visual, drag Year and Month from the Calendar Table.
3 Replies
- Ashish_Mathur
Super User
Hi,
I don't think that is possible. You will need to create a Month No. or order column and then sort the months by that column.
- Alirezam
Hi Thank you but the problem is when I sort the data on "Month No" I miss the sorting of years! where exactly should I sort the "month no."? in table mode?
- Ashish_Mathur
Hi,
Assuming there is a proper date column in your Data Table, create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table. In the Calendar Table, extract Year by using =YEAR(Calendar[Date]). Extract Month by using =FORMAT(Calendar[Date],"mmmm"). Create another 2 column Table with Month names in one column and numbers (from 1 to 12) in another column. Build a relationship from the Month Name column of the Calendar Table to the Month name column of this new 2 column Table. In the Calendar Table, write this calculated column formula =RELATED(Month_order[Order]). Now click on any month cell in the Calendar Table and go to Sort By > New column. To your visual, drag Year and Month from the Calendar Table.
