How to Get Month Number in Power BI

Working with dates in Power BI is a daily task, and getting the month number from a date column is one of the most common requirements you'll face. Whether you need it for sorting, filtering, or calculations, knowing how to extract this simple number is a fundamental skill. This guide will walk you through a few easy ways to get the month number in Power BI using both DAX and the Power Query Editor.

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 Would You Need the Month Number?

You might wonder why you’d need a dedicated column for just the month number when you already have a full date. There are several very practical reasons:

• Sorting Month Names Chronologically: This is the most common reason. If you create a chart using month names (e.g., "January," "February"), Power BI will default to sorting them alphabetically ("April," "August," "December"). A month number column lets you fix this with a couple of clicks so "January" always comes first.
• Filtering by Month: A month number column makes it easy to filter your reports to show data for a specific month across multiple years. For example, you can analyze sales performance for every "October" over the last five years.
• Creating Calculations: You can use the month number in DAX formulas for conditional logic. For instance, you could group months into seasons or apply different logic for the first half of the year versus the second half.

Method 1: Using DAX to Add a Month Number Column

DAX (Data Analysis Expressions) is Power BI’s formula language, used for creating calculated columns and measures. Adding a month number column with DAX is incredibly quick and is the best approach when your data is already loaded into your Power BI model.

Here are two simple DAX functions to get the job done.

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

Using the MONTH() Function

The MONTH() function is the most direct way to get a month's number as an integer (1 for January, 2 for February, etc.).

Here’s how to use it:

1. Navigate to the Data view by clicking the table icon on the left-hand navigation pane.
2. In the Fields pane on the right, select the table that contains your date column.
3. From the ribbon at the top, click on New Column under the "Table tools" or "Column tools" tab.
4. A formula bar will appear. Enter the following DAX formula. Be sure to replace 'Sales Data'[OrderDate] with your own table and column names.

Month Number = MONTH('Sales Data'[OrderDate])

1. Press Enter. Power BI will instantly add a new column to your table containing the corresponding month number for each date.

The result is a clean column with numbers from 1 to 12. Since this returns a whole number, it's perfect for mathematical operations and for use in sorting other columns.

Using the FORMAT() Function

The FORMAT() function is more versatile and can give you the month number in different text formats, such as a two-digit number with a leading zero (e.g., "01" for January).

Follow the same steps as above to create a new column, but use one of these formulas instead:

• To get a single-digit number (1-12): Use "M" as the format string. This produces the same result as the MONTH() function, though the data type will be text instead of a number.

Month Num (Text) = FORMAT('Sales Data'[OrderDate], "M")

• To get a two-digit number with a leading zero (01-12): Use "MM" as the format string. This can be great for consistency in visual displays or for file-naming conventions.

Padded Month Number = FORMAT('Sales Data'[OrderDate], "MM")

A Quick Note: The FORMAT() function always returns a text string, not a number. This means you can't perform math on the result without first converting it. For sorting purposes, however, it works just fine as text.

Method 2: Using the Power Query Editor

If you prefer to shape and clean your data before loading it into the Power BI model, the Power Query Editor is the place to be. This method becomes part of your data transformation steps and runs automatically every time your data refreshes.

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

The User-Friendly GUI Method

Power Query has a brilliant user-friendly interface that lets you do this without writing a single line of code.

1. From the main Power BI window, click on Transform data in the Home ribbon. This will open the Power Query Editor.
2. In the Queries pane on the left, select the query that contains your date column.
3. Select the date column header.
4. Go to the Add Column tab in the ribbon.
5. In the "From Date & Time" group, click the Date dropdown menu. A list of options will appear.
6. Hover over Month, and then click Month from the sub-menu.

That's it! Power Query adds a new column named "Month" containing the month number (1-12). It automatically sets the data type to Whole Number. Finally, click Close & Apply in the top-left corner to load your changes.

Using a Custom Column for More Control

What if you want the padded, two-digit month number ("01", "02") within Power Query? The built-in button only gives you the integer. For this, you can use a "Custom Column."

1. With your query open in the Power Query Editor, go to the Add Column tab and click Custom Column.
2. A new window will pop up. Give your new column a name, such as "Padded Month".
3. In the "Custom column formula" box, enter the following M formula, making sure to replace [OrderDate] with your actual date column name.

Date.ToText([OrderDate], "MM")

1. Click OK. Power Query will add a new column with the two-digit month number as a text value.

Practical Application: Sorting Your Month Names Correctly

Now, let's put that new month number column to work to solve the most common Power BI frustration: sorting month names in chronological order.

Imagine you have a bar chart showing sales by month. By default, it looks like this:

(e.g., April, August, December, February...)

Here’s how to fix it in under a minute:

Step 1: Make Sure You Have Month Name and Month Number Columns

Use the methods above to create a Month Number column. If you don't already have one, create a Month Name column using this simple DAX formula:

Month Name = FORMAT('Sales Data'[OrderDate], "MMMM")

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 2: Set the Sort Order

1. Go to the Data view in Power BI.
2. In the Fields pane, find and click on your table to expand it.
3. Select the Month Name column (the one with names like "January," "February"). This tells Power BI which column's sorting behavior you want to change.
4. A Column tools tab will appear in the top ribbon. Click on it.
5. Click the Sort by column button.
6. From the dropdown menu that appears, select your Month Number column.

Power BI will work for a moment, and that’s it. Now, go back to your report. Any visual that uses your "Month Name" column will now be sorted correctly (January, February, March...). It's a game-changing fix that makes your reports professional and easy to read.

Final Thoughts

Figuring out how to extract a month's number is a simple but powerful technique in Power BI. Whether you prefer using a quick DAX formula like MONTH() for calculations or the Power Query Editor to build it into your data pipeline, this skill fixes common sorting problems and gives you more control over your reports.

Ultimately, data analysis is about getting answers fast, not getting stuck wrangling dates or learning complex tools. At Graphed, we've automated away these tedious manual steps so you can focus on insights. Instead of creating calculated columns and setting sort orders, you can simply ask a question like, "Show me revenue by month for the last year," and our AI builds a perfect, automatically sorted dashboard for you in seconds. If you're tired of spending your time on setup and want to get straight to the answers, give Graphed a try.