Sorting the month name sequentially in Power BI
We dedicate this blog post to a very practical and simple but very useful solution that will save you a ton of frustration when working with names of months. When working with month names in Power BI we run into the problem that the months of the year are not ordered sequential. See the image below:
Visuals in Power BI do offer the functionality to sort by a certain parameter. In our visualization, we are displaying the QTY received for the year as well as the month of the year. Thus the two parameters we can sort by is month and QTY. Let’s sort by month:
We still see that the graph below is still not sorted in the correct manner:
Another option that Power BI offer to sort columns by is to sort descending and ascending as seen below:
Let’s sort the month column in ascending order:
We see that the above visualization still doesn’t give us the desired result. What Power BI is doing here is that it is sorting the month’s column in alphabetical order as the month’s column is stored as a text field. Thus Power BI is behaving in the correct manner. We have to find a more suitable solution for this.
Let’s have a closer look at our dataset. We see here that we have a column with the month name, which is a text field. We also have a column called ‘Month Number’. The ‘Month Number’ column has a data type of a whole number. So from this, we can see that the month name can be ordered in sequential order if we select the ‘Month’ column and order it by the ‘Month Number’ column.
To do this first select the ‘Month’ column.
Now we must head over to the Modeling tab and select the Sort by Colum as ‘Month Number’.
Now let’s go back to our visualization to see the result. Perfect, we can see that the months are now sequentially ordered.
We can see that Power BI have a lot of interesting ways to overcome some of the simpler functionality problems that we may run into.