How to Add Month in Power BI

Displaying your data by month is fundamental to tracking business trends, but getting Power BI to cooperate isn't always straightforward. Month names have a bad habit of sorting alphabetically instead of chronologically, a frustrating roadblock for anyone building a report. This guide will walk you through a few reliable methods to add and correctly sort month-based data, from quick Power Query tricks to the best-practice approach using DAX and a dedicated date table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Automatic Date Hierarchies Aren't Always Enough

When you load a date column into Power BI, it automatically creates a built-in date hierarchy (Year, Quarter, Month, Day). While convenient for a quick drag-and-drop analysis, this "auto date/time" feature can have some downsides. It creates hidden tables for each date column, which can bloat your file size and complicate your data model if you have many dates to manage.

More importantly, relying on the automatic hierarchy gives you less control over your time-based calculations. For robust, flexible, and accurate time intelligence (like calculating year-to-date or same-period-last-year metrics), creating your own columns and even your own dedicated date or calendar table is the industry best practice. It provides a single, reliable source for all things time-related in your report and is the foundation for the most powerful features Power BI has to offer.

Method 1: Using Power Query to Add Month Columns

If you prefer a low-code approach and want to perform this transformation before your data is even loaded into the Power BI model, the Power Query Editor is your best friend. It provides a simple, graphical interface for adding month names and numbers.

This is a great starting point if your main goal is just to quickly add month columns without writing any formulas.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions

Follow these steps to add a month name and month number using Power Query:

1. Open Power Query: From the Power BI main screen, click the Transform data button on the Home ribbon. This will launch the Power Query Editor.
2. Select Your Date Column: In the query editor, find the table containing your date data and click on the header of the date column to select it.
3. Add the Month Number: With the date column selected, navigate to the Add Column tab in the ribbon. Click the Date dropdown, hover over Month, and select Month. A new column named "Month" will appear, containing the month number (1 for January, 2 for February, and so on).
4. Add the Month Name: Reselect your original date column again. Go back to Add Column > Date > Month and this time select Name of Month. This will add another column containing the full name of the month ("January", "February", etc.).
5. Close & Apply: Once you've added your new columns, click the Close & Apply button in the top-left corner of the Power Query Editor. Your new columns will now be available in your data model.

Pros: This method is very user-friendly, involves no DAX code, and performs the transformation as a step in the data loading process.

Cons: While simple, you will almost certainly run into the dreaded alphabetical sorting issue when you use the "Name of Month" column in a visual. We'll solve that in a later section.

Method 2: Creating Calculated Columns with DAX

Using DAX (Data Analysis Expressions) is the more powerful and flexible way to add time-related columns to your model. Instead of adding columns in Power Query, you add them directly to your table in Power BI’s Data view. This approach keeps your data transformation logic centralized within the data model itself.

It's the preferred method when you need more customization, such as adding month abbreviations or combining the month and year.

Writing the DAX Formulas

First, navigate to the Data view by clicking the table icon on the left-hand pane in Power BI Desktop.

From here, you can add your new columns.

1. Creating a Month Name Column

Select the table you want to add the column to. From the Table tools or Column tools tab, click New column. An input bar will appear where you can enter your DAX formula. To create a column with the full month name, use the FORMAT function:

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

Here’s a breakdown:

• Month Name: The name of your new column.
• FORMAT(): A versatile DAX function that converts a value to text in a specified format.
• 'Sales'[OrderDate]: The date column from your table (replace with your own table and column name).
• "MMMM": The format code for the full name of the month (e.g., "January").

2. Creating a Month Number Column

This column is essential for proper sorting. Click New column again and use this simple formula:

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

The MONTH() function extracts the month number (1-12) from the date.

3. Creating Other Useful Variations

DAX's flexibility makes it easy to create other common formats. For a three-letter month abbreviation:

Month Abbrev = FORMAT('Sales'[OrderDate], "MMM")

For a combined month and year format (like "Jan 2024"), which is perfect for chart axis labels:

Month Year = FORMAT('Sales'[OrderDate], "MMM YYYY")

Now that you've created these columns, it's time to fix the sorting order.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Solving the Alphabetical Month Sorting Problem

If you add your new Month Name column to a chart axis, you will immediately notice that Power BI sorts it alphabetically: April, August, December, February... This happens because the column is a text data type, and Power BI has no innate understanding that "February" should come after "January."

Fortunately, the fix is simple and is precisely why we created the Month Number column.

Step-by-Step Sorting Instructions

1. Select the Column to Sort: In the Data view, find and click on the column header of your month name column (e.g., Month Name). This is the column that needs to be sorted correctly.
2. Find the 'Sort by Column' Tool: With the column selected, the Column tools tab will appear in the ribbon at the top. Click on the Sort by Column button.
3. Choose the Sorting Column: A dropdown list of all other columns in the table will appear. Select the Month Number column you created earlier.

That's it! Your visuals will now display the months in the correct chronological order: January, February, March, and so on. This setup tells Power BI, "When you display the Month Name text, use the values in the Month Number column to determine the sort order."

Best Practice: Creating a Dedicated Date Table

While adding calculated columns to your existing tables works, the gold standard for analytics in Power BI is to create a separate, dedicated "Date Table" or "Date Dimension." This table serves as a single source of truth for all date-related attributes in your model.

Why Bother with a Date Table?

• Consistency: Ensures all your visuals use the same logic for dates, months, and years.
• Performance: Simplifies your data model and can improve report performance.
• Time Intelligence Functions: It's a prerequisite for using advanced time intelligence DAX functions like DATESYTD, TOTALMTD, and SAMEPERIODLASTYEAR.

You can generate one easily with a single DAX expression.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create a Date Table with DAX

1. Navigate to the Data view, and in the Home ribbon, click New table.
2. Enter the following DAX formula into the formula bar. The CALENDARAUTO() function automatically scans all date columns in your model and creates a table with a single continuous date column spanning the minimum and maximum years it finds.

Date Table = CALENDARAUTO()

Press Enter. You'll now have a new table named "Date Table" containing a single column called "Date" with an unbroken sequence of dates.

1. Add Your Helper Columns: Now, add the month-related calculated columns we created earlier (Month Name, Month Number, Month Abbrev, etc.) to this new Date Table, referencing its own [Date] column. For example:

Month Name = FORMAT('Date Table'[Date], "MMMM")

Month Number = MONTH('Date Table'[Date])

1. Mark as Date Table: Right-click on your new table in the Fields pane and select Mark as date table > Mark as date table. In the pop-up, choose the Date column. This officially designates it for use with time intelligence functions.
2. Create a Relationship: Go to the Model view (the third icon on the left pane). Create a relationship by dragging the Date column from your new Date Table and dropping it on the date column in your primary data table (e.g., OrderDate in your 'Sales' table).

Now, for all your charts and visuals, you should use the columns from your new Date Table. This clean, centralized approach will make your Power BI reports much more scalable and reliable.

Final Thoughts

Mastering how to add and sort month columns correctly is a milestone skill for any Power BI user. We've covered the quick setup in Power Query, the more flexible DAX calculated columns, and finally, the robust industry standard of implementing a dedicated date table. Whichever method fits your current need, you now have the tools to ensure your time-based reports are both accurate and easy to read.

This process highlights the specific, sometimes manual, steps required within BI tools to get your data just right before you can analyze it. Many teams spend a huge chunk of their week just wrangling data and configuring reports - and that's the exact problem we built Graphed to solve. We automate this grunt work by connecting directly to your marketing and sales platforms. You can simply ask questions in plain English like, "Show me campaign revenue by month for the last year," and Graphed instantly builds the correctly sorted chart for you. There's no DAX to learn and no manual sorting configurations needed, so you can focus on insights instead of setup.