Sort months in chronological order

[ArtFi]

I know this question has been asked a lot, but my specific issue is that I have lot of different data for one chart (which I select with a slicer) and not every year starts with January, eg if it's May, May gets number 1 in the column next to it (all different options are in the same column, so each month and therefore each month number are mentioned multiple times).

That way, when I try to sort the months by month number (the most common solution that I encountered), I get the following error: [...] You can't have more than one value in 'Number Month' for the same value in 'Month'[...]. I guess that's because the same month in the first column sees multiple values for month number in the other column (as the year does not always begin with the same month like I said).

Does anyone have an idea for a possible workaround?

Thanks, Arthur

[jjupiter100]

I think I have a much simpler answer.  The 2 main issues are the first letters of each month of the year are not in alphabetical order, so Excel always wants to put April first instead of January.  And if you try to give each month a number, October, November, and December present problems because 10, 11, and 12 get put before 1 (which would be January).

So, however you're going to label your months (typically January = 1, February = 2 and so on), make October = 91 (because September would have equaled 9), make November = 92 and December = 93. 

This gets your pivot table sorted correctly and when you go to make your graph, you can just rename each of those fields to be whatever you want.  And most importantly, they'll stay in the same order you want them.

[ArtFi]

Thanks everyone for the effort, I changed month numbers to eg 201505 and changed the month name to May-15, this way it will always sort right, no matter what year or month, and moreover, there is no more confusion between name month and number!

[v-haibl-msft]

@ArtFi

I’m a little confused about one thing. If one year starts with May, why you need May to return number 1 in the column next to it?

If you use following column formula for MonthNum, it will return number 5 and you’ll not encounter the problem you have now.

MonthNum =
MONTH ( Table1[Date] )

Best Regards,

Herbert

[ArtFi]

@v-haibl-msft May needs to return 1, because otherwise January of the next year would be 1, February 2 and so on. If I'd try to sort now, January of eg 2016 would before May 2015 in the chart, you see?

[v-haibl-msft]

@ArtFi

As CheenuSing mentioned, you’d better create a calendar table with similar formula as below if you don’t have.

CalendarTable =
CALENDAR ( "1/1/2015", "12/31/2016" )

Then create relationship between these two tables with date key.

Create three columns in the Calendar table. Select MonthName column and make it sorted by YearMonth column.

YearMonth = 
YEAR ( CalendarTable[Date] ) * 100
+ MONTH ( CalendarTable[Date] )

MonthName = 
FORMAT ( CalendarTable[Date], "mmmm yyyy" )

Year = 
YEAR ( CalendarTable[Date] )

Now you should be able to sort months as below. Make sure you select the MonthName column in Calendar table for Axis.

Best Regards,

Herbert

[anandav]

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

[anandav]

Further 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.

[FrugalEconomist]

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]

@FrugalEconomist,

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.

[FrugalEconomist]

Hi Anandav,

Thanks for your response. 

Unfortunately, I am using direct query so those formula's aren't supported. Instead I used sql to generate the month text and month ranking in my data set.

It's sorting in the splicer, but not filtering in the visual. Which is mind-boggling as it's the same data!

[anandav]

Hi @FrugalEconomist,

I tried doing the same but using Excel. I have a date field, I added columns yyyymmdd and month name.

In Power BI Data view, I selected MonthName column and clicked on 'SortByColumn' option and selected DateIndex. The month was sorted correctly.

I have a before screenshot for comparison.

I think this is similar to having the DateIndex column in SQL.

If you could post your date, dateindex and month name fields may be that could shed some light.

[anandav]

Hi @@v-haibl-msft,

Thank you for this post. I had exactly the same problem and followed your steps and solved the issue. Great post and thanks a lot!

One question:

I had a Month Name column and when tried to use the YearMonthIdex column to sort the MOnth Name, it gave the following error:

“.. You cannot sort the Month Name column by YearMonthIndex. You cannot have more than one value in YearMonthIndex for the same value in Month Name.”

But as suggested in your post when I created a another column with YearMonth I could sort that column using the YearMonthIndex column.

Any idea why the original Mont Name column did not allow me to sort using the YearMonthIndex?

[Baskarved]

Hi,

@v-haibl-msft , Could you please let us know why are you multiplying the year value in "YearMonth" colum.Since if we change any  number instead of 100 the expected result is not coming.

YearMonth = 
YEAR ( CalendarTable[Date] ) * 100
+ MONTH ( CalendarTable[Date] )

[CheenuSing]

@ArtFi

Please clarify

a) Do you have any Calendar or Date Table ?

b) How has this been built- is it continuous from the Minimum of Fact Table date and upto the maximum of Fact Table Date

c) What is the business rule to be applied to start a new year with different months as the first month number. 

It will be good to  have a sample of your data along with the data model to come out with appropriate solution.

Cheers

CheenuSing 

[CheenuSing]

@ArtFi

Can you post some sample data and output you expect.

Cheers

CheenuSing

[KGrice · Accepted solution]

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.