How to order months chronologically in Power BI

Change from sorting months Apr, Aug, Dec to Jan, Feb, Mar:

This method creates a separate month table and brings it into Power BI to order months chronologically.

I have a video on my YouTube channel explaining this method or you can use this easy-to-follow guide:

STEP 1
Create and save a month table like the one below, it can be in any format,
Here’s a ready made file in excel you can use to save you time typing out the month names!

First column contains the month name as it appears in your data
Second column is a number indicating the order the months should be sorted in
(so for financial year order April would be numbered 1)

STEP 2
Load the table into Power BI using the ‘Get Data’ button

STEP 3
Next you need to sort the month column in this new month lookup table:
1. navigate to the month table in the data tab
1. select the month column
2. go to the ‘sort column’ in the ‘column tools’ tab
3. select ‘month numb’ from the drop down list


STEP 4
Next set up a relationship join between the month lookup and the table containing the monthly data:
1. click on the ‘Manage relationships’ button in the ‘Home’ tab
2. click on ‘New’ to set up a new relationship
3. from the first drop down select the month look up table
4. from the second drop down select the table containing monthly data
5. highlight the columns in each table that contain the month names
6. OK


STEP 5
Now create your visual using the month name from the month look up table, instead of the month name column in your data table, and your months will be ordered chronologically, like this:


Processing…
Success! You're on the list.

Cover photo by Glen Carrie on Unsplash

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: