Select Distinct Logo Clear Background

Business Analytics Blog

How to sort dates in Power BI

How to sort dates in Power BI

Working with dates can sometimes be a little challenging, this quick guide shows you how to sort dates in Power BI

Its not always obvious how to do simple things like sort months in calendar order

Here we show some simple tips using a date dimension table

If you want to know how to create a date dimension table in Power BI so you can follow this example

Add a date dimension table in Power BI

Sorting Dates Properly (sort dates chronologically in Power BI)

We usually want to sort dates chronologically in Power BI

By default, month names tend to be stored as text fields

This causes issues when we want to use them in reports

In this example the default sort order is alphabetical, which is not what we want

a table showing a list of months sorted alphabetically, a typical problem when a beginner wants to sort dates in Power BI

How to  sort one column by another column in Power BI

In Power BI Desktop, go to the data tab and select the column that you want to change the sort order of.

In this case we are selecting the ‘MonthNameLong’ column, as by default it is sorting alphabetically

So, to sort dates in Power BI we need to sort the alphabetical fields such as month name, by an associated value based field such as month number

To do this, Click the ‘MonthNameLong’ field heading to select it

On the ribbon, select sort by column and choose ‘MonthNumber’

Sorting the date by month number gives use the effect of sorting the date chronologically

Sort by column settings, used in this examples to sort dates in Power BI

Now all of the visuals using this field now use the Month Number to sort the values throughout the report

example

No of days per month in Power BI, showing the month names sorted in chronological order

Suggested date sorting settings in Power BI

Whilst we are looking at dates, we should do similar for the Day names too as these are also stored as text and will sort alphabetically

If you have used our date dimension table or similar, then you should apply these settings to other fields

Table of fields to sort by in Power BI

How to sort dates in Power BI: a Conclusion

You now know how to sort dates in Power BI.

Many users are initially frustrated by these date sorting challenges in Power BI, and often they decide to use a date formatted field as a quick work around

But at the expense of readability, showing a chart with month number ’10’ does solve the sorting problem, but the user has to think what it means.

It is much more user friendly to show ‘October’ or ‘OCT’

Now that you have learned that a column can be sorted by a different column this is a very convenient way of sorting date related columns the correct way

It can also be applied to types of data too, e.g. if you have an ‘Ordinal’ data type such as Gold, Silver, Bronze, you could add a sort order column to that with values such as 1,2,3 which can be sorted

Watch our YouTube video on how to sort dates in Power BI

Subscribe to our channel to see more Timesavers

Select Distinct YouTube Channel

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert