Sorting By Month in Chronological Order in Power BI

Month name in sorting order in Power BI, a quick fix for it

If you have seen my previous email about creating your first report in Power BI, chances are high that after you created your first visual, it will look like the below where April is the first month and September the last. The reason for that is because Power BI does sort every column alphabetically, unless specified otherwise.

Creating your first visual for monthly sales and profit

Data tab view of underlying data

If we go to Data tab to check the underlying data (see above), we see that there are two columns related to month, one of them is the Month Name and the other one is Month Number. Considering that we want to see months in the correct order, we will use Month Number as a sorter for Month Name. Simply select Month Name column and go to Sort by column and choose Month Number.

Sorting month name based on month number

Now, if you go back to report view, you will see that month is sorted in the correct way.

Month name sorted properly based on month number

One might say, this was easy, how about the cases when month number is missing and all you have is a date, how do you get the month number from it? While there are a lot of different options on how to do it, you can follow those simple steps described below.

You need to open Power Query Editor. In order to do that, you go to Transform data tab and then choose Transform data.

Opening your Power Query Editor

From the Power Query Editor, select fx which will add a new step in the applied steps section. This section keeps track of all the transformations that were made to the underlying data after they were brought in Power BI.

Adding a new fx step in Power Query Editor

We will be using a very simple M formula (read more about M here), paste this one to the formula bar and hit Enter.

= Table.AddColumn(#"Changed Type", "Month no", each Date.Month([Date]), Int64.Type)

To translate it in plain English, M formula says: “Hey, add a column on top of the last step Changed Type, give it a name Month No and get the data from Date column, make sure you extract only the month and bring it as a number.”

Adding new column based on M formula

After adding column with month no, follow the same process we did previously to sort Months based on the Month no column.

If you feel like this was not enough and you want to step up the game and learn more, check out my other email where I wrote on How to Create a Visual Tooltip.

In case you are struggling with this, do let me know in the comments section below.

Cheers!!