In this article, I’m going to demonstrate how to sort months chronologically in Power BI. Visualizing sales data in a time period is one of the most influential ways of reporting. Often, at times it is essential that you design charts that show the trend or growth of a metric over time. The time period can be anything for example days, weeks, months, or years. It basically gives you the idea of how the metric has increased or decreased over the specific period.
In Power BI, you can also visualize your metrics by a specific selected period. This is usually implemented by drawing line charts that show the trend over time, or by using vertical bar charts that show specific metrics in the time period, or even simply using a tabular matrix to display the results textually. For the sake of this article, we will consider only the third scenario i.e. tabular matrix for simplicity. However, the same solution can be made to work for any type of chart which contains a time period in it.
Sometimes when you import data into Power BI, and specifically if you have months or quarters available as a textual data in the original data source (from a flat-file), then Power BI cannot understand whether the imported field is an actual date field (days/month/year, etc.) or just simple text data. In such a case, after the import, the months or quarters are sorted alphabetically, rather than chronologically which is an error depending on the requirements and something not so relevant in developing trend reports. In this solution, we will first learn how to reproduce the error, and then see how to make the necessary changes in the data model to resolve this error and sort months chronologically in Power BI.
Reproducing the Error
Let us first try to reproduce the error and then I shall explain how to sort the months chronologically in Power BI. In order to replicate the error, we need to create a simple CSV file as shown in the figure below.
Figure 1 – CSV Dataset
As you can see in the figure above, we just have two simple columns – Month and Sales. The Month lists all the values starting from “January” to “December” and corresponding Sales values along with it. Once you have created the CSV file, the next step is to open Power BI and connect it to this dataset.
Open Power BI Desktop and follow the steps below to fetch this data into the Power BI data model:
-
Select on Get Data and select Text/CSV from the menu that appears
Figure 2 – Get Data in Power BI
-
Browse for the file that you just created in the previous step and click on Open
Figure 3 – Browse for Dataset
-
In the next dialog that appears, verify the data and click on Load
Figure 4 – Load data into Power BI Model
- You will see the data has been loaded into Power BI
- Click on Table from the Visualizations Pane and drag and drop the fields into a table as shown in the figure
-
In the new table that is created, you can see that the months are now sorted alphabetically
Figure 5 – Creating the table
-
In the Power BI Desktop, select Transform Data and then click on Transform Data
Figure 6 – Transform Data
- In the Power Query Editor that opens, navigate to the Add Column tab that opens
- Select Custom Column and enter the formula as shown in the figure below
- For the name of the custom column, I’m using “Date” since this column is going to store dummy date values
-
In the formula for the custom column, use the following: = “1 ” & [Month] & ” 2020″
Figure 7 – Adding Dummy Date Column
-
So basically, what we are trying to achieve is just create a dummy date value by adding “1” as the date and “2020” as the year value to the month that already exists
Figure 8 – New Date Column Added
-
The next step is to convert this new field, Date to a date datatype. Right-click on the column and select Change Type and then select Date
Figure 9 – Changing the Data Type
-
You can see that the data type and values for this column have changed
Figure 10 – Changed the Data Type
- We will now add one more custom column that will extract the month number from this field. Click on Add Columns and then on Custom Columns
-
Provide the name of the field as “MonthNumber” and formula as “= Date.Month([Date])” and click OK
Figure 11 – Adding new MonthNumber Column
-
You’ll now see one more column added in the dataset
Figure 12 – Added new MonthNumber Column
-
We will change the data type of this field to the Whole Number. Right-click on the MonthNumber, select Change Type and then select the Whole Number
Figure 13 – Change the Data Type to Whole Number
-
Since we have the desired field MonthNumber in our data model, we can remove the dummy date field that we created in our previous steps. Right-click on the Date column and select Remove
Figure 14 – Removing the dummy Date column
-
Finally, you can close the Power Query Editor window by navigating to the Home and select Close and Apply
Figure 15 – Closing the Power Query Editor
- Click on the Data tab on the left-hand pane
- Select the Month column and then select Sort by Column in the Sort pane in the toolbar above
-
In the drop-down that appears, select MonthNumber and navigate to the Reports tab
Figure 16 – Sort Month By Month Number
-
You can now see, that the months are being sorted chronologically as opposed to alphabetically
Figure 17 – Months sorted chronologically in Power BI
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021
As you can see in the figure above, when you drag and drop the Month field into the Values section of the table, all the months are sorted but alphabetically. However, in usual reference, we often tend to analyze months or rather any periods chronologically and not alphabetically. So, in order to sort months chronologically in Power BI, we would need to make some transformations in the data model. These transformations can be done using the Power Query Editor that is available within the Power BI Desktop tool.
Solution – Sort months chronologically in Power BI
Now that we know what the actual error is all about, let’s go ahead and make the necessary changes in the data model to sort the months chronologically in Power BI. The idea is to create a dummy date value based on the Month data that is available in the dataset. Once the date values are available, we can just extract the Month Number and sort the Month column using the Month Number field. You can follow the steps provided below.
Selecting Column to Sort Months Chronologically in Power BI
Now that we have made the necessary changes in our data model to sort months chronologically in Power BI, the final step is to set the sorting order on the Month in ascending order of MonthNumber. Please follow the steps below to sort the months.
Additionally, if you want to hide the MonthNumber field, you can just right-click on it and select Hide.
Figure 18 – Hide MonthNumber field
You’ll have your dataset as was in the original CSV file but the months are sorted chronologically.
Figure 19 – Months Sorted Chronologically in Power BI
Conclusion
In this article, I have explained how to sort months chronologically in Power BI. I have also mentioned the steps to replicate the error and then provided step-by-step guidance on how to resolve the issue and finally get the desired results.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das