Forum Discussion
Sort months in chronological order
Hi ArtFi. Can you combine your desired month number with a year prefix to give it a list of one-to-one values? For example, if you want 2015 to be ordered Jan then Feb etc., they would be numbered 201501, 201502. If you wanted 2016 to be ordered Mar, Jan, Feb, then March would still get 201601, then 201602 for Jan and 201603 for Feb. You'd just need to find some way to define the custom sort order in your column, either through a conditional column or maybe hard-coding the values in an Excel table.
If it's just that a year doesn't start with January because there is no January for that year (e.g., no sales for that month), you could still use the YYYYMM format to sort your months, and if the year starts with March, it won't hurt your chronological sort order anyway.
Skilled SharerHi v-haibl-msft,
Thanks to you suggestion I solved the month sort order but I again have hit a problem with Week order.
As per below image, I am trying to sort the WeekDuration column by WeekNumOfYearIndex but it is giving an error.
“We cannot sort the 'WeekDuration' column by 'WeekNumOfYearIndex'. There Can't be more than one value in 'WeekNumOfYearIndex' for the same value in 'WeekDuration'. Please choose a different column for sorting or update the data in 'WeekNumOfYearIndex'."
Data type of WeekDuration is Text.
Data type of WeekNumOfYearIndex is Whole Number.
What works:
1. I can sort the MonthName (MMMM) column by Month (month number) column.
2. I can sort the MonthNameYear (MMMM YYYY) column by MonthYearIndex (YYYYMM) column (as per your reply in this article).
What doesn't work:
1. Sorting the WeekDuration (DD MM YY - DD MM YY) by WeekNumOfYearIndex.
WeekNumOfYearIndex = (Dim_Date[MonthYearIndex]*100)+Dim_Date[WeekOfYear]
WeekDuration = Day(Dim_Date[StartOfWeek]) & " " & FORMAT(Dim_Date[StartOfWeek], "MMM YY") & " - " & Day(Dim_Date[EndOfWeek]) & " " & FORMAT(Dim_Date[EndOfWeek], "MMM YY")
Any help on this will be greatly appreciated.
Thanks
Skilled SharerFurther update:
Just keep things consistent, I used the WeekStartDate across all columns.
I my view, this ensures that a given value in WeekDuration will have the same value repetaed in WeekNumOfYearIndex.
In my earlier sample when the month changes sometimes a given value in WeekDuration will have different values in WeekNumOfYearIndex for the same WeekDuration.
But even after correcting and ensuring that a given WeekDuration does not have different values in WeekNumOfYearIndex, I still get the same error as explained in earlier post.
- anandav
Further to the above, I deleted all the dates and just used 1 month date data set. When sorted WeekDuration by WeekNumOfYearIndex column it worked. Not sure why, but now it works now for the full date set as well.
FrugalEconomistHelper III
I'm getting a very particular inconsistency with month sorting. I have a calculated month text and month sort in SQL.
When I add it to my dashboard, the slicer shows the month sorting properly, but on the X axis of the visual graph, it's all kind of bonkers (not even in alphabetical order)....
Any advice would be greatly appreciated!
Thanks
- anandav
Did you follow the steps outlined the first page of this thread by v-haibl-msft (09-08-2016 09:19 PM)?
If you followed the steps then the months should sort in propoer order. Since that post has detail steps with screenshots it should be pretty easy to understand.
If you still have problem after doing those steps and let me know and I will try to help. I'm not an expert but will do what I can to help.