How to Sort by Date in Power BI

Nothing disrupts the flow of a good dashboard like a bar chart where "April," "August," and "December" show up before "January." Trying to show your month-over-month sales becomes a confusing mess when your chart is sorted alphabetically instead of chronologically. This is a classic Power BI puzzle, but thankfully, it's one with a straightforward solution. This article will show you exactly how to fix this by creating a proper date table and using the "Sort by Column" feature to put your months (and days) back in the right order.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Power BI Sorts 'April' Before 'January'

The problem isn't a glitch, it's just Power BI doing exactly what you've asked of it, even if you didn't realize it. When you create a visual using a column that contains text, like one you've named "MonthName," Power BI's default behavior is to sort that text alphabetically. It sees "April," "August," "December," and "February" as simple strings of text, not as chronological time periods.

Without additional context, the software has no way to understand that "January" has a chronological value of 1 and should come before "February" (value 2). So, it falls back on the only logic it has for text: A-to-Z sorting.

This results in charts that look something like this:

• April
• August
• December
• February
• January
• July
• ...and so on.

This is not just visually jarring, it makes it impossible to spot actual trends or patterns in your time-series data. The story your data is trying to tell gets completely lost in translation. The same logic applies if you try to sort by the day of the week - you’ll get "Friday," "Monday," "Saturday," and "Sunday," which is hardly useful for analyzing weekly performance.

To fix this, we need to manually provide Power BI with the correct sorting context. The proper and most robust way to do this is by creating a dedicated date table.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: The Best Practice - Build a Dedicated Date Table

While you might be tempted to just add a "MonthNumber" column to your main data table, the professional standard in Power BI development is to create a separate, comprehensive date table (often called a calendar table). This might seem like extra work at first, but it establishes a foundation that makes your entire report more powerful, flexible, and easier to manage.

What is a Date Table?

A date table is a simple table where each row represents a single day. Crucially, it contains multiple columns that describe that day in various ways - the year, the month number, the month name, the quarter, the day of the week, and more. Creating this table once gives you a single source of truth for all your date-based analysis, which is invaluable as your reports get more complex.

You can create a date table in a few ways, but the two most common methods are using DAX or Power Query.

Method 1: Create a Date Table with DAX

Using Data Analysis Expressions (DAX) is often the fastest and most flexible way to generate a date table. It involves writing a formula that programmatically creates the table for you.

Here’s how to do it:

1. From the "Report" view in Power BI Desktop, navigate to the "Modeling" tab in the top ribbon and click on "New Table."
2. A formula bar will appear. Copy and paste the following DAX code into the bar:

Date Table = ADDCOLUMNS (     CALENDARAUTO(),     "Year", YEAR ( [Date] ),     "MonthNumber", MONTH ( [Date] ),     "MonthName", FORMAT ( [Date], "mmmm" ),     "MonthNameShort", FORMAT ( [Date], "mmm" ),     "QuarterNumber", QUARTER ( [Date] ),     "Quarter", "Q" & QUARTER ( [Date] ),     "DayOfWeekNumber", WEEKDAY ( [Date], 2 ), // Using 2 makes Monday=1, Sunday=7     "DayOfWeekName", FORMAT ( [Date], "dddd" ) )

Understanding the DAX Code:

• CALENDARAUTO(): This powerful function automatically scans all the date columns in your entire data model, finds the earliest and latest dates, and creates a continuous list of dates covering that full range. This ensures your date table always includes every day present in your data.
• ADDCOLUMNS(): This function takes the table generated by CALENDARAUTO() and adds new columns to it.
• YEAR(), MONTH(), QUARTER(), WEEKDAY(): These are standard DAX functions for extracting specific parts from a date.
• FORMAT(): This function lets you specify custom formatting. "mmmm" gives you the full month name (e.g., "January"), while "dddd" gives you the full day name (e.g., "Monday"). This is how we get our troublesome text-based columns in the first place! The key difference is we are also creating the numeric columns (MonthNumber, DayOfWeekNumber) we need for sorting right alongside them.

Once you press Enter, Power BI will execute this script, and your new "Date Table" will instantly appear in the "Data" pane on the right-hand side.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Create a Date Table in Power Query

If you're more comfortable using a graphical interface than writing code, creating a date table in the Power Query Editor is a great option. It’s a very visual process that achieves the same result.

Follow these steps:

1. On the "Home" ribbon, click "Transform data" to open the Power Query Editor.
2. In the Power Query home tab, click "New Source," select "Blank Query."
3. Right-click the new query in the "Queries" pane on the left and rename it to something descriptive, like "Date Table."
4. With the query selected, click "Advanced Editor" in the "Home" ribbon.
5. Delete any existing code and paste the following M language script. Note: you’ll need to adjust the start and end dates to fit your data.

let     StartDate = #date(2023, 1, 1),     EndDate = #date(2024, 12, 31),     NumberOfDays = Duration.Days(EndDate - StartDate) + 1,     DateList = List.Dates(StartDate, NumberOfDays, #duration(1, 0, 0, 0)),     #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),     #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),     #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),     #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),     #"Inserted Month" = Table.AddColumn(#"Inserted Year", "MonthNumber", each Date.Month([Date]), Int64.Type),     #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "MonthName", each Date.ToText([Date], "MMMM"), type text),     #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "DayOfWeekNumber", each Date.DayOfWeek([Date], Day.Monday)+1, Int64.Type),     #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "DayOfWeekName", each Date.ToText([Date], "dddd"), type text) in     #"Inserted Day Name"

When you click "Done," Power Query will show you the fully generated table. You can also generate this step-by-step using the menus. For example, instead of using code for the "Inserted Year" step, you could just select the "Date" column, go to the "Add Column" tab, click "Date," then "Year," and then "Year." This makes Power Query an intuitive way to build exactly what you need.

Once you're happy with your table in Power Query, click "Close & Apply" in the top-left corner.

Step 2: Connect Your Date Table to Your Data

Whether you used DAX or Power Query, you now have an independent date table. The next crucial step is to teach Power BI how this new table relates to your actual business data (e.g., your sales table, web traffic table, etc.).

1. Click on the Model view icon on the far left of Power BI Desktop. It looks like three connected boxes.
2. You will see your tables represented as boxes. Find your newly created Date Table and your main data table (let's call it Sales).
3. Click and hold the Date column in your Date Table and drag your mouse over to the corresponding date column in your Sales table (e.g., OrderDate).
4. Release the mouse. Power BI will automatically create a line connecting them, representing a relationship. It should be a one-to-many relationship (indicated by a 1 on the Date Table side and an asterisk * on the Sales side). This tells Power BI that for every one unique day in the calendar, there can be many sales.

This relationship is what allows you to use your shiny new date table to slice, filter, and analyze your sales data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Using the 'Sort by Column' Feature

With all the pieces in place, we can finally perform the action that fixes our original problem. This is where you tell Power BI exactly how to sort your text-based month names.

1. Click on the Data view icon on the far-left navigation bar (it looks like a small spreadsheet).
2. From the "Data" pane on the right, select your Date Table.
3. Now, in the table view itself, click on the header of the column you want to sort correctly. For our example, click the header for the MonthName column. You'll see the whole column gets highlighted.
4. With that column selected, a special "Column tools" tab will appear in the top ribbon. Click on it.
5. Look for a button named "Sort by Column." This is the magic button!
6. Click "Sort by Column," and a dropdown list of all the other columns in your date table will appear.
7. Select MonthNumber from the list.

A small loading icon may appear, but that's it! You've successfully instructed Power BI that whenever it needs to display the MonthName column, it should use the MonthNumber column to determine the sort order. It will now see "January" but sort it based on the number 1, "February" based on 2, and so on.

Go back to your report. You’ll see that your visuals using "MonthName" are now perfectly sorted chronologically: January, February, March... all the way to December.

Bonus Tip: Sorting Days of the Week

The exact same principle applies to sorting other date attributes, like the day of the week. To stop your visuals from sorting 'Friday' and 'Monday' first, simply repeat the process:

1. In the Data view, select your DayOfWeekName column.
2. Go to the "Column tools" ribbon.
3. Click "Sort by Column."
4. Select your DayOfWeekNumber column from the dropdown.

Your charts will now order the days of the week starting with Monday (or Sunday, depending on how you configured the WEEKDAY function) instead of alphabetically.

Final Thoughts

In short, forcing Power BI to sort date fields chronologically isn't complicated once you understand the logic. It's about giving the program the right context by creating a dedicated date table and then using the "Sort by Column" feature to map text-based fields like month names to their corresponding numeric values for proper ordering.

If building date tables and managing data models in Power BI still feels a bit time-consuming, you're not alone. The setup can sometimes get in the way of getting fast answers. This is one of the reasons we built Graphed. We wanted to talk to our data using plain English, not clicks and code. You can connect your marketing and sales sources in seconds and ask questions like "Show me a chart of Shopify sales this year by month," and it builds the dashboard for you without having to manually configure sorting for date fields.