Graphed LogoGraphed
Login

How to Create a Month Column in Power BI

Cody Schneider•

Working with dates is a foundation of almost every report, and grouping your data by month is one of the most common requirements you'll encounter. Whether you're tracking monthly sales targets, website traffic patterns, or operational expenses, getting months to display correctly in your Power BI visuals is essential. This guide will walk you through several methods for creating a dedicated month column in Power BI, including how to fix the dreaded alphabetical sorting problem.

Why a Dedicated Month Column Is So Important

You might be wondering why you can't just drop your standard date field into a chart. You actually can! Power BI's automatic date hierarchy feature will often try to break down the date into Year, Quarter, Month, and Day for you. However, this automatic behavior can be unpredictable and doesn't offer much flexibility in formatting.

Creating a dedicated, separate column for the month gives you precise control. You can choose the format (e.g., "January", "Jan", or "1"), use it consistently across multiple visuals, and most importantly, tell Power BI exactly how you want it sorted. This deliberate approach leads to more readable, stable, and professional-looking reports.

Method 1: Using DAX to Create a Month Column

DAX (Data Analysis Expressions) is Power BI's formula language. It's incredibly powerful for adding new calculated columns and measures to your data model. Using a simple DAX function is one of the quickest ways to create your new month column directly within the familiar Power BI interface.

Let's assume you have a table named 'Sales' with a date column called 'OrderDate'.

Step 1: Create a New Column

First, navigate to the Data View in Power BI by clicking the table icon on the left-hand pane. Select the table you want to add the month column to (in our case, the 'Sales' table). In the 'Column tools' ribbon that appears at the top, click on New column.

This will open up the formula bar where you'll enter your DAX expression.

Step 2: Write the DAX Formula

Now, you'll use the FORMAT function to pull the month from your date column and display it as text. This function is extremely versatile and allows you to specify the exact text format you want.

To Get the Full Month Name (e.g., "January")

For the full name of the month, use the format code "MMMM". In the formula bar, type:

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

Press Enter. Power BI will instantly populate a new column called "Month Name" with the corresponding full month name for every date in your 'OrderDate' column.

To Get the Abbreviated Month Name (e.g., "Jan")

If you prefer a shorter version to save space in your visuals, use the format code "MMM".

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

This creates a column with three-letter abbreviations like "Jan", "Feb", "Mar", and so on.

To Get the Month Number (e.g., "01", "02")

Sometimes, all you need is the number of the month. For this, you can use either the FORMAT function or the simpler MONTH function.

Using FORMAT with "MM" ensures a leading zero for single-digit months, which can be useful for sorting text fields.

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

Alternatively, the MONTH function returns the month as a pure number (1-12) without a leading zero.

MonthNumber_int = MONTH('Sales'[OrderDate])

This numeric version will be crucial for fixing the sorting issue, which we'll cover in a moment.

Method 2: Using the Power Query Editor

The Power Query Editor is Power BI's data transformation engine. It's the ideal place to add columns and shape your data before it's loaded into your data model. Changes made here are part of the data refresh process, making your model clean and efficient.

Step 1: Open the Power Query Editor

From the main Power BI window, navigate to the Home ribbon tab and click Transform data. This will open the Power Query Editor in a new window.

Step 2: Select Your Date Column

In the Power Query Editor, find and click on the header of the date column you want to work with (e.g., 'OrderDate'). This tells Power BI which column you're using as your source.

Step 3: Add the Month Column

Navigate to the Add Column tab in the ribbon. You'll see a section named 'From Date & Time'. Click the Date dropdown button.

Here you'll see a menu with several options to extract parts of the date. To create your month column, hover over Month and choose from the fly-out menu:

  • Month: This will give you the month number (1-12).

  • Name of Month: This will give you the full month name ("January", "February", etc.). Power Query is clever enough to localize this name based on your system's settings.

When you make your selection, Power Query automatically adds a new column to your table with the desired month information. It's often a good practice to rename this new column to something intuitive, like "Month Name" or "MonthNo". You can do this by double-clicking the column header.

When you're finished, click Close & Apply on the Home ribbon to load your changes into the Power BI model.

The Critical Fix: Sorting Months in Chronological Order

You've successfully created your "Month Name" column. You drag it into a bar chart, and your heart sinks. The chart shows the months sorted alphabetically: April, August, December, February... This is probably the most common frustration for new Power BI users.

The reason is simple: your new "Month Name" column is a text field, and Power BI defaults to sorting text fields alphabetically. To fix this, you need to tell Power BI to sort this text column based on a different, numeric column. This is where your month number column comes in.

The Solution: "Sort by Column"

Whether you created your month number column with DAX or Power Query, the steps to fix the sorting are the same.

  1. Create a helper column: Make sure you have both a month name column (e.g., "Month Name" with values like "January") and a month number column in the same table (e.g., "Month Number" with values 1-12). Let's say you've created MonthNumber_int using the DAX MONTH() function.

  2. Select the column to sort: Go to the Data View in Power BI. Find and click on the column you want to display, which is your "Month Name" column. Do not select the number column.

  3. Apply the sorting rule: With the "Month Name" column selected, go to the Column tools ribbon at the top. Click the Sort by column dropdown. From the list, choose your helper numeric column, "MonthNumber_int".

Nothing will visibly change in the data table itself. But now, when you return to your report and use the "Month Name" column in a visual, it will be sorted correctly: January, February, March, and so on. This setting is now permanently part of your data model.

Best Practice: Creating a Dedicated Date Table

While adding month columns directly to your fact table (like a 'Sales' table) works perfectly fine, a more robust and scalable best practice is to use a dedicated Date Table, sometimes known as a Calendar Dimension.

A Date Table is a separate table that contains one row for every single day in your desired date range. Each row contains that date plus all the attribute columns you might need for reporting: year, quarter, month name, month number, day of the week, fiscal year, etc.

Here’s why it's a better approach:

  • Single Source of Truth: If you have multiple data tables (e.g., Sales, Inventory, Marketing Spend), a single Date Table can connect to all of them, ensuring consistent time-based analysis across your entire report.

  • Enables Time Intelligence: Power BI's powerful time intelligence DAX functions (like DATESYTD, SAMEPERIODLASTYEAR, etc.) rely on a properly configured, unbroken date table to work correctly.

  • Simplified Model: It keeps your main data tables cleaner. Instead of adding 10 different date-related columns to your 'Sales' table, you keep them neatly organized in one place.

You can create a basic Date Table very quickly with DAX. Go to the Data View, click New table in the Home ribbon, and paste in a formula like this:

Date Table = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR ( [Date] ), "Quarter", "Q" & FORMAT ( [Date], "q" ), "Month Number", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "MMMM" ), "Month Short", FORMAT ( [Date], "MMM" ) )

This code scans your model for the earliest and latest dates to automatically generate a calendar. It then adds columns for Year, Quarter, Month Number, and both long and short Month Names. After creating it, remember to mark it as a date table by selecting it and finding the "Mark as date table" option in the Table tools ribbon, setting the validation on the [Date] column. And of course, use the "Sort by Column" trick on "Month Name" using "Month Number" as the sort key!

Final Thoughts

Creating and properly sorting a month column is a fundamental skill that transforms messy data into clear, chronological insights. By using DAX for quick calculations, Power Query for data preparation, or adopting the best practice of a dedicated date table, you can master how time is presented in your reports. Ultimately, these techniques give you the control needed to build more intuitive and professional dashboards.

Building these individual columns and configuring sort orders in Power BI is a common part of the reporting workflow, but it’s still manual. At Graphed, we've automated this entire process. When you connect your data sources to Graphed and ask "show me monthly sales for last year as a bar chart," we instantly generate the visual with months already correctly sorted for you. There's no need to write DAX or configure sort columns, our AI understands context and handles the data modeling behind the scenes, giving you answers in seconds.