The Month Sorting Problem
This can be a thorny problem. The issue is when you use the format function to extract the month from the date. Proud that you have accomplished that, you then insert the same month into a matrix visualisation or slicer - only to find April at the top and September at the bottom.
You can create a formula similar to the following to extract the month name from the date:
=FORMAT([Date],"mmmm")
(Image removed)
Isn't the above a pain? You simply want to sort by month. What you can learn here will not only help you when you're sorting by month. It will also help when you want to sort any column by any other column. This can help with Month Names, Days of the Week and even regional areas. Let's start off with Month numbers then we'll try days of the week. At the end we'll have a look at a couple of issues that are common to sorting columns. Why they occur and how to overcome them.
Created a new calculated column called month number and enter in the following:
=MONTH([Date])
the data should look like follows:
(Image removed)
Now that we've done that, ensure that the Month field is selected.
Then choose Month Number from the Sort By Column at the top of the screen.
(Image removed)
Swap back to your visualisation and see the months are in the correct order.
(Image removed)
Now we have the month order we know and love. Well, probably not love but at least it's something we're familiar with. The astute of you will remember that I said, apart from sorting by month number, you can use the sort-by-column feature to sort any column by any column and that much is true. Next we'll use this same approach to sort by Weekday. You know? The day of the week. (Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday).
We shall first use the DAX FORMAT function to get the day of the week in text format. The. we will use the WEEKDAY function to extract the week day number. We need this so that we can correctly sort the text field. Have go at this example::
First create a calculated column to and enter a formula similar to the following where [Date] would be the date column or field for the data you wish to analyse.
=FORMAT([Date],"dddd")
You will see the following displaying the the full day of the week.
(Image removed)
Pop that data into a slicer visualisation and you'll get the following:
(Image removed)
So as you can see, we have encountered the same issue. The days of the week are sorted alphabetically. Let's solve this problem in exactly the same way as we did the months.
Add a new column with the name Weekday Number and enter in the following:
= WEEKDAY([Date Sold],2)
The number "2" after the column tells the function to take Monday as the first day of the week.
You should see a column that looks like:
(Image removed)
Now click on the Weekday column and sort the column by Weekday number. Just like we did with the month number. Then swap back to the slicer and your weekdays should be in the correct order:
(Image removed)
As you can see, the sort by column is very handy however there are a couple of warnings. The first of these warnings you may come across is this:
More than one value in one column for the same value in another column
(Image removed)
The first of the problems you may encounter is the above. Basically, Power BI needs to have a matching values in both columns. So when, as we have tried in the above example, you try to sort a column that doesn't have matching values you naturally get an error. As above we are trying to match Week Number with Weekday. This can't work! This is because Week day 1 could have numerous week number values. Basically, the number of times that Monday appears in the year. To get this to work you can only use Weekday number. Which returns a value of 1 - 7 depending on what day of the week it is.
Sorting by a columns that is already sorted
The other error you may receive is:
(Image removed)
This is another error you might come across. You, obviously, can't sort one column by the column it's already being sorted by. In the above example I was trying to sort Seasons. I used a SWITCH function work out the month and the seasons which looked something like:
Season = SWITCH([Month No],
1,"Winter",
2,"Winter",
3,"Spring",
4,"Spring",
5,"Spring",
6,"Summer",
7,"Summer",
8,"Summer",
9,"Autumn",
10,"Autumn",
11,"Autumn",
"Winter"
)
Then I tried to work out the order of the seasons by using this same column to assign numbers to the seasons. Something like the SWITCH statement below. This seems like it works, but it doesn't :(
Season Number = SWITCH([Season],
"Winter",4,
"Spring",1,
"Summer",2,
"Autumn",3,
4
)
I can see the error when I try to sort the Seasons by the Season number. When I do this I get the "Sort by another column" error as you can see above.
A possible solution to this problem is you do a longer switch statement looking to month again, such as:
Season No = SWITCH([Month No],
1,4,
2,4,
3,1,
4,1,
5,1,
6,2,
7,2,
8,2,
9,3,
10,3,
11,3,
4
)
This way month 1 is season 4 (which is winter), month 2 is also season 4. Month 3 is season 1 or spring and so on. Because this column is linking to another column that is not dependant on this column for sorting you can use it to sort season.
Hope this makes sense and that you got something out of this. If you want more information check out our one day Power BI beginners training course, that should get you up to speed.