Sorting Month Names Chronologically in Microsoft Power BI Reports

//Sorting Month Names Chronologically in Microsoft Power BI Reports

Sorting Month Names Chronologically in Microsoft Power BI Reports

Sorting Month Names Chronologically in Microsoft Power BI Reports

Often times, in Power BI, you’re adding dates to your reports and would prefer to use the more formal month name rather than the month number. After all, it can give your reports a more polished look. Trouble is, Month Name fields are text fields, and therefore Power BI has no choice but to sort them alphabetically. I mean, who wouldn’t want their date sorted by April, then August, then December, then February, right? Well, there’s a trick that will let you sort dates the way they should be sorted, whether you’re using the month name or number.

For the sake of the exercise, let’s create a date table in Power BI Desktop.

Go to Modeling, New Table

clip_image002

Replace “Table = “ with the following code to create a table called “Date”:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
“DateAsInteger”, FORMAT (

[Date], “YYYYMMDD” ),
“Year”, YEAR ( [Date] ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “ddd” ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “/Q” & FORMAT ( [Date], “Q” )
)

Now let’s create a simple table visualization.

clip_image004

And then drag the MonthNameLong field into the table.

clip_image006

We get the list of months, but they are listed alphabetically rather than chronologically.

clip_image008

We can change the way the table sorts, however. First, click on the MonthNameLong field in the Fields bar. Don’t check the box, simply select the field so it looks like it does here:

clip_image010

Then return to the Modeling tab, and you’ll see the “Sort By” button is no longer dim. Click it, and you’ll see that “MonthNameLong” is the currently selected “sort by” field:

clip_image012

Click “Monthnumber” instead, and voila!

clip_image014 clip_image016

It’s important to note that this change should be made in the Report viewer, not the Table, to ensure chronological sorting appears on the report.

By |2018-12-12T18:35:00+00:00January 26th, 2018|Uncategorized|0 Comments

About the Author:

Leave A Comment