How to Extract Month from Date Column in Power BI

Working with dates in Power BI often means you need to pull out specific parts, like the month, to analyze trends over time. Whether you want to see monthly sales figures or track website traffic seasonality, extracting the month is a fundamental step. This guide will walk you through several simple methods to isolate the month from a date column using DAX formulas 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 Separating the Month is So Useful

Before jumping into the step-by-step instructions, it's helpful to understand why you’d want a dedicated month column in the first place. A simple date column (e.g., “01/15/2024”) is great for chronological records, but less useful for aggregated analysis. By creating a separate column for the month, you unlock powerful reporting capabilities.

• Analyze Seasonality: Retail businesses can easily spot holiday sales spikes in November and December. SaaS companies might see a dip in July and August as clients go on vacation. A month column makes these yearly patterns obvious.
• Create Clear Visualizations: Grouping data by month is perfect for visuals like bar charts or line graphs. A chart showing “Total Sales by Month” is one of the most common and valuable reports for any business.
• Build User-Friendly Slicers: Adding a slicer based on the month name allows users to filter the entire report page to see data for just “January” or “Q4” (by selecting Oct, Nov, Dec), making your dashboards much more interactive.
• Compare Performance: You can more easily compare performance for the same month across different years. A matrix visual could pit January's sales from this year directly against last year's performance, helping you track growth.

In short, a month column transforms your raw date data into a powerful dimension for drilling down into your business’s performance.

Method 1: Using DAX to Add a Month Column

One of the most common ways to add columns in Power BI is by using Data Analysis Expressions (DAX). Creating a calculated column with DAX adds the new column directly to your data model. This means the month data is stored within your file and is available to use in any chart, table, or slicer. This method is done inside the main Power BI interface after your data has been loaded.

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

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

Most of the time, you'll want the month's full name for your charts and slicers to make them easy to read for your audience. Here's how to do it.

1. Navigate to the Data view by clicking the table icon on the left-hand side of Power BI Desktop.
2. Select the table that contains your date column from the Data pane on the right.
3. From the top ribbon, click on the Table Tools tab, and then select New Column. This will open up the formula bar.
4. In the formula bar, type the following DAX formula. Replace 'YourTable'[YourDateColumn] with the actual names of your table and date column.

Month Name = FORMAT('YourTable'[YourDateColumn], "MMMM")

Let's break down this simple formula:

• Month Name = is what you are naming your new column.
• The FORMAT function converts a value into text in a specified format.
• 'YourTable'[YourDateColumn] is the date column you are referencing.
• "MMMM" is the key part. This format code tells DAX to return the full name of the month.

Pro Tip: Use Different Format Codes

The FORMAT function is very flexible. You can use different codes to get different month formats:

• "MMM" will give you the abbreviated month name (e.g., "Jan", "Feb").
• "MM" will give you the two-digit month number with a leading zero (e.g., "01", "02", "11").
• "M" will give you the month number without a leading zero (e.g., "1", "2", "11").

Extracting the Month Number (e.g., 1, 2, 3)

Sometimes, all you need is the month number. This is especially useful for sorting, as we’ll see in a moment. Extracting the month number is even easier.

1. Follow the same steps as above to create a new calculated column.
2. Use this DAX formula instead:

Month Number = MONTH('YourTable'[YourDateColumn])

The MONTH() function is straightforward: it takes a date as input and returns the month as a number from 1 to 12. Super simple, and very useful.

The Best of Both Worlds: Creating Columns for Proper Sorting

If you create a bar chart and add your new Month Name column, you'll immediately notice a major problem: Power BI sorts the months alphabetically by default (April, August, December, February...). That's not helpful!

To fix this, you need to tell Power BI the correct chronological order. The solution is to use both the month name and the month number column together. Here is the best-practice workflow:

1. Create two columns:
2. Select the Month Name Column: In the Data view, click on the "Month Name" column header to select it.
3. Apply the Sort Order: With the "Month Name" column selected, go to the Column Tools tab in the top ribbon.
4. Click on the Sort by column dropdown menu.
5. From the list that appears, choose your "Month Number" column.

That's it! Nothing will visibly change in your data table, but a rule has been set in the background. Now, whenever you use the "Month Name" column in a chart or slicer, Power BI will automatically sort it chronologically: January, February, March, and so on. This is a crucial step for almost any report involving month names.

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: Using the Power Query Editor

The Power Query Editor is Power BI’s engine room for cleaning, shaping, and transforming your data before it gets loaded into your data model. Using Power Query is ideal when you want to establish your data structure once and have those rules applied every time your data is refreshed. For tasks like extracting parts of a date, it offers a user-friendly, no-code alternative to DAX.

Step 1: Open the Power Query Editor

From the Power BI Desktop's Home tab on the main ribbon, click the Transform data button. This will launch a new window for the Power Query Editor.

Step 2: Select Your Date Column

In the Queries pane on the left, select the query (your data table) you want to modify. Then, in the main data preview window, find and click on the header of the date column to select it.

Step 3: Add a New Column for the Month

With your date column selected, the best practice is to add a new column rather than transforming the existing one. This preserves your original date column for other calculations.

1. Go to the Add Column tab in the ribbon at the top.
2. Click the Date dropdown menu. A list of transformations will appear.
3. Hover over Month to see several options.

Now, you can choose what you want to extract:

• Choosing Month will create a new column with the month number (1-12).
• Choosing Name of Month will create a new column with the full month name ("January," "February," etc).

Simply click your desired option, and Power Query will instantly add the new column to your table. You can rename the column by double-clicking its header. A huge advantage of using Power Query for this is that when you extract the "Name of Month," Power BI automatically adds another column for the month number and applies the proper sort order in the background. You don’t have to do it manually like you do with DAX.

When you're finished, click Close & Apply in the Home tab to load the changes back into your Power BI data model.

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

DAX vs. Power Query: When to Use Which?

Both methods will give you a month column, so which one should you choose? It often comes down to your workflow and your project's specific needs.

Use DAX Calculated Columns when:

• You need flexibility after data is loaded. DAX columns are calculated based on other data in your model and can react to user interactions like filters and slicers.
• You prefer formulas. If you're comfortable with DAX and want to keep all your calculations visible within the main Power BI editor, this is a great choice.
• Your dataset is not extremely large. For most datasets, the performance difference is negligible, but on massive models, calculated columns can use more memory and slow down file performance.

Use the Power Query Editor when:

• You are doing one-time data cleanup. Power Query is part of your data import and preparation (ETL) process. It shapes the data before it enters your model. This is generally more efficient.
• You want better data model performance. Transformations in Power Query are compressed more effectively, leading to smaller file sizes and potentially faster reports, especially with large datasets.
• You prefer a click-based interface. Power Query's menus let you transform data without writing a single line of code, which is great for users who are just getting started.

For something as simple as extracting a month, either method is perfectly fine. However, getting comfortable with Power Query for core data shaping is a great habit for building efficient and scalable reports in the long run.

Final Thoughts

Extracting the month from a date column is a fundamental skill in Power BI that opens the door to creating insightful, time-based analysis. Whether you choose the formula-driven flexibility of DAX or the structured, click-based interface of the Power Query Editor, you can have a tidy, sortable month column ready for your reports in just a few minutes.

While these methods are powerful, building out robust reporting dashboards across different marketing and sales platforms can easily become a chore. It is often a constant cycle of data prep, transformation, and dashboard building before you can even get to the actionable insights. Here at Graphed, we want to simplify that entire process. With Graphed, you can connect tools like Google Analytics, Shopify, Facebook ads, and Salesforce in seconds. Instead of performing these steps manually, you can just ask in plain English, "Show me site sessions versus sales revenue by month for the last year," and our tool will build a live-updating dashboard for you in moments. We handle all the background steps like data extraction and transformation so you can skip straight to analyzing the bigger picture.