How to Change Date to Month in Power BI

Displaying data by day can be overwhelming, but grouping it by month reveals trends that are much easier to understand. If you're working with date fields in Power BI, converting them into month names or numbers is a fundamental step for creating clean, insightful reports. This article will walk you through a few simple methods to handle this, using both the Power Query Editor and DAX formulas.

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 Bother Formatting Dates as Months?

Imagine looking at a sales report with 365 individual data points for each day of the year. It's cluttered and hard to spot any meaningful patterns. Now, picture that same report summarized into 12 data points - one for each month. Suddenly, you can easily identify your best sales months, spot seasonal trends, and compare performance over time.

Converting dates to months is less about changing data and more about improving clarity. It helps you:

• Summarize data effectively: Aggregate daily transactions into monthly totals, averages, or counts.
• Identify trends: See how metrics like website traffic, revenue, or leads perform month-over-month.
• Improve readability: Make charts and tables cleaner and more intuitive for your audience.

Fortunately, Power BI provides user-friendly tools to make this transformation quick and easy.

Method 1: Using the Power Query Editor

Power Query is the ideal place to handle data transformations like this. It cleans and shapes your data before it even loads into the Power BI model, which is a best practice for performance and organization. Here’s how to do it step-by-step.

Step 1: Open the Power Query Editor

From the main Power BI Desktop window, go to the Home tab on the ribbon and click on Transform data. This will open the Power Query Editor, where you can see all your data tables.

Step 2: Select Your Date Column

In the Power Query Editor, select the table that contains your date field from the left-hand Queries pane. Then, find and click on the header of the date column you want to convert. For this example, let's use a column named ‘Order Date’.

A good habit is to preserve your original date column by right-clicking the column header and choosing Duplicate Column. This gives you a copy to work with, leaving the original untouched.

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: Extract the Month Name

Working with your now duplicated column, go to the Add Column tab in the Power Query ribbon. This tab lets you create new columns based on existing ones.

1. Click the Date dropdown menu.
2. Hover over Month.
3. From the flyout menu, select Name of Month.

Power BI will immediately add a new column to your table containing the full month name (e.g., "January", "February", "March") for each corresponding date.

Step 4: Create the Month Number (for Sorting) and Abbreviation

Month names are great for visuals, but Power BI will sort them alphabetically by default ("April", "August", "December"). That's not very useful. To fix this, you need a month number column to sort by.

Back in Power Query, select your original date column again. Go to Add Column > Date > Month > Month. This will create a column with the corresponding month number (1, 2, 3, etc.). You’ll use this later to fix the sorting order.

If you need a three-letter abbreviation like "Jan" or "Feb", you can create a custom column:

1. Go to the Add Column tab and click Custom Column.
2. Give your new column a name, such as "Month Abbr".
3. In the formula box, enter the following M code formula:

Date.ToText([Order Date], "MMM")

Replace [Order Date] with the name of your actual date column. Click OK. You now have a tidy three-letter month abbreviation.

Once you're done, click Close & Apply in the top-left corner to load your changes into the Power BI data model.

Step 5: Fix the Chronological Sorting

Now for the most important step! Back in the main Power BI report view, you need to tell Power BI how to sort your month names correctly.

1. In the Data pane on the right, find and click on your new "Month Name" column. This will activate the Column tools tab in the ribbon.
2. In the "Column tools" tab, click the Sort by column button.
3. From the dropdown list, select the "Month Number" column you created.

That's it! Now, whenever you use the "Month Name" column in a visual, it will automatically sort chronologically from January to December instead of alphabetically.

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 DAX (Data Analysis Expressions)

Alternatively, you can create new month columns directly in your data model using DAX formulas. This is useful if you can't use Power Query (for example, when connected to a live data source) or if you prefer to keep calculations within the model itself.

Step 1: Open the Data View

In the main Power BI Window, click on the Data icon in the far-left navigation pane to view your data table.

Step 2: Create a New Calculated Column

Select the table you want to add the column to. In the Table tools tab that appears on the ribbon, click New column. This will open up the DAX formula bar just below the ribbon.

Step 3: Write the DAX Formulas

Enter the following DAX formulas, pressing Enter after each one to create the column. Remember to replace ‘YourTable’[YourDateColumn] with the actual names from your data.

• To get the full month name:

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

• To get the month number (for sorting):

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

• To get the three-letter month abbreviation:

Month Abbr = FORMAT('YourTable'[YourDateColumn], "MMM")

Just like with Power Query, these formulas will add new columns to your table. Once created, you still need to set the sorting order for your month name and month abbreviation columns using the "Sort by column" instructions in Step 5 from the previous section.

Bonus: Displaying Month and Year Together

Often, you’ll need to display the month and year together (e.g., "Jan 2023", "Feb 2023") to properly track trends over longer periods. Otherwise, data from January 2023 and January 2024 would be incorrectly grouped together.

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 Power Query

The easiest way is with a custom column using this formula:

Date.ToText([YourDateColumn], "MMM yyyy")

But again, this creates a sorting problem ("Apr 2023" comes before "Feb 2023"). To fix this, create another custom column to use for sorting, like this:

SortKey_YYYYMM = Date.Year([YourDateColumn]) * 100 + Date.Month([YourDateColumn])

This formula produces a number like 202301 for January 2023 and 202312 for December 2023, which sorts perfectly. Use the "Sort By Column" feature on your "MMM yyyy" column, and select this SortKey column.

Using DAX

Use these two DAX calculated columns:

1. For the display text:

Month Year = FORMAT('YourTable'[YourDateColumn], "MMM yyyy")

1. For sorting:

SortKey_YYYYMM = FORMAT('YourTable'[YourDateColumn], "YYYYMM")

Then, select the "Month Year" column and set it to sort by the "SortKey_YYYYMM" column.

Final Thoughts

Transforming date columns into months is a simple but vital part of building effective reports in Power BI. Whether you prefer the upfront data preparation in Power Query or the modeling flexibility of DAX, the key is to always create a separate numeric column to ensure your months are sorted chronologically, not alphabetically.

Manually creating columns and tweaking sorting logic in tools like Power BI is a common first step, but it’s rarely the end goal. We built Graphed to help you skip these steps and get straight to the insights. Rather than clicking through transformation menus, you can connect your data sources and simply ask, "Show me product sales by month for the last twelve months," and instantly get back a live, interactive chart. We handle the conversions and technical details in the background so you can spend your time exploring data, not formatting it.