Aveek Das
Changing the Data Type

How to sort months chronologically in Power BI

March 16, 2020 by

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.

Sample Dataset for Sort Months Chronologically in Power BI

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:

  1. Select on Get Data and select Text/CSV from the menu that appears

    Get Data in Power BI

    Figure 2 – Get Data in Power BI

  2. Browse for the file that you just created in the previous step and click on Open

    Browse Sort Months Chronologically in Power BI Dataset

    Figure 3 – Browse for Dataset

  3. In the next dialog that appears, verify the data and click on Load

    Load data into Power BI model

    Figure 4 – Load data into Power BI Model

  4. You will see the data has been loaded into Power BI
  5. Click on Table from the Visualizations Pane and drag and drop the fields into a table as shown in the figure
  6. In the new table that is created, you can see that the months are now sorted alphabetically

    Sort Months Chronologically in Power BI table

    Figure 5 – Creating the table

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

    1. In the Power BI Desktop, select Transform Data and then click on Transform Data

      Transform Data

      Figure 6 – Transform Data

    2. In the Power Query Editor that opens, navigate to the Add Column tab that opens
    3. Select Custom Column and enter the formula as shown in the figure below
    4. For the name of the custom column, I’m using “Date” since this column is going to store dummy date values
    5. In the formula for the custom column, use the following: = “1 ” & [Month] & ” 2020″

      Adding Dummy Date Column to sort months chronologically in power bi

      Figure 7 – Adding Dummy Date Column

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

      New Date Column Added

      Figure 8 – New Date Column Added

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

      Changing the Data Type

      Figure 9 – Changing the Data Type

    8. You can see that the data type and values for this column have changed

      Data Type Changed

      Figure 10 – Changed the Data Type

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

    10. Provide the name of the field as “MonthNumber” and formula as “= Date.Month([Date])” and click OK

      Adding new MonthNumber Column

      Figure 11 – Adding new MonthNumber Column

    11. You’ll now see one more column added in the dataset

      Added new MonthNumber Column

      Figure 12 – Added new MonthNumber Column

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

      Change the Data Type to Whole Number

      Figure 13 – Change the Data Type to Whole Number

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

      Removing the dummy Date column

      Figure 14 – Removing the dummy Date column

    14. Finally, you can close the Power Query Editor window by navigating to the Home and select Close and Apply

      Closing the Power Query Editor

      Figure 15 – Closing the Power Query Editor

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

      1. Click on the Data tab on the left-hand pane
      2. Select the Month column and then select Sort by Column in the Sort pane in the toolbar above
      3. In the drop-down that appears, select MonthNumber and navigate to the Reports tab

        Sort Month By Month Number

        Figure 16 – Sort Month By Month Number

      4. You can now see, that the months are being sorted chronologically as opposed to alphabetically

        Months sorted chronologically in Power BI

        Figure 17 – Months sorted chronologically in Power BI

      Additionally, if you want to hide the MonthNumber field, you can just right-click on it and select Hide.

      Hide MonthNumber field

      Figure 18 – Hide MonthNumber field

      You’ll have your dataset as was in the original CSV file but the months are sorted chronologically.

      Months Sorted Chronologically in Power BI

      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.

      Aveek Das
PowerBI

About Aveek Das

Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interest are SQL Server, SSIS, and SSAS. In his leisure, he likes to keep his skills sharp by writing articles on his blog and other forums. He is also an amateur photographer and hops around to do street photography. You can find him on LinkedIn View all posts by Aveek Das

10,759 Views