How to Concatenate Month and Year in Power BI

Creating a combined month-and-year field is a surprisingly common task in Power BI. Whether you want to label a chart axis clearly or create a user-friendly slicer, getting "January 2024" to show up and, more importantly, sort correctly is essential. This tutorial will walk you through the most effective ways to concatenate month and year using both DAX and Power Query.

Why Combine Month and Year in the First Place?

Before diving into the "how," let's quickly touch on the "why." By default, if you bring a date field into a chart, Power BI will break it down into a hierarchy of Year, Quarter, Month, and Day. This is useful, but often you just want to see a continuous timeline of months, like Jan 2023, Feb 2023, Mar 2023, etc.

Creating a dedicated "Month-Year" column gives you:

• Improved Readability: "Jan 2024" on a chart axis is much clearer than just "Jan," which could be from any year in your dataset.
• Correct Chronological Sorting: A simple text combination often sorts alphabetically (April, August, December...), which is useless for a timeline. We'll fix that.
• Simpler Slicers and Filters: It allows users to easily select specific month-year periods without navigating a complex date hierarchy.

The Foundation: A Proper Date Table

First things first. The best practice for any time-based analysis in Power BI is to use a dedicated date or calendar table. A date table is a simple table containing a continuous list of dates, along with columns for attributes like Year, Quarter, Month Name, Day of Week, etc. This is the gold standard for time intelligence.

While you can create new columns directly on your sales or transactional table, using a separate date table is more flexible, efficient, and powerful. All the methods below will assume you're adding the new calculated column to your existing date table.

Method 1: Using DAX to Create a Calculated Column

The most direct way to combine month and year is by creating a calculated column using Data Analysis Expressions (DAX). This happens directly within your Power BI data model after the data has been loaded.

Follow these steps:

1. Navigate to the Data View in Power BI (the second icon on the left-hand pane).
2. From the list of tables on the right, select your Date Table.
3. In the "Table tools" ribbon at the top, click New column. This will open the formula bar.

Step 1: Write the DAX formula for the text column

In the formula bar, type the following DAX expression. We'll use the FORMAT function, which is perfect for converting dates into specific text formats.

Month-Year = FORMAT('Date'[Date], "MMM yyyy")

Hit Enter, and you'll see a new column filled with values like "Jan 2024," "Feb 2024," etc. The format strings in the second argument are very flexible.

Here are a few popular variations:

• "Mmm-yy" creates "Jan-24"
• "MMMM yyyy" creates "January 2024" (the full month name)
• "yyyy-MM" creates "2024-01"

Step 2: Solve the Sorting Problem (This is Crucial!)

If you use this newly created text column in a chart, you'll immediately see a problem. It will sort alphabetically: April 2024, August 2024, December 2024... not chronologically.

To fix this, we need to create a second, "helper" column that Power BI can use for sorting. This column should contain a numerical representation of the month and year.

Still in your Date Table, create another new column with this formula:

Month-Year Sort = FORMAT('Date'[Date], "yyyymm")

This formula creates a text column with values like "202401", "202402", etc. Because these values increase sequentially, they will sort chronologically, exactly what we need.

Step 3: Connect the Columns with "Sort by Column"

Now, we just need to tell Power BI to use our numeric sort column when ordering our friendly text column.

1. Still in the Data View, make sure your Date Table is selected.
2. Click on the header of your "Month-Year" text column (the one with "Jan 2024").
3. Go to the Column tools tab at the top.
4. Click the Sort by Column button.
5. From the dropdown list, select your "Month-Year Sort" column.

That's it! Your visuals will now display the user-friendly "Jan 2024" format but will sort them chronologically based on the "202401" helper column.

Method 2: Using the Power Query Editor

An alternative – and often preferred – method is to create these columns in the Power Query Editor. This means the transformation is applied during the data load process, before the data even enters your model. This is considered a best practice for data shaping as it can lead to better model performance and efficiency.

Here’s how to do it:

1. In the Home tab of the main Power BI window, click on Transform data to open the Power Query Editor.
2. In the list of queries on the left, select your Date Table.

Step 1: Add a Custom Column for the Month-Year Text

In the Power Query Editor, we'll use "Custom Column" to build our new fields.

1. Navigate to the Add Column tab at the top.
2. Click Custom Column.
3. In the dialog box, name your new column (e.g., "Month-Year").
4. Enter the following M formula:

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

Click OK. You will see your new column appear. Just like with DAX, you can change the format string (e.g., "Mmm-yy", "MMMM yyyy"). After creating the column, make sure to set its data type to "Text" if it isn't already.

Step 2: Add a second custom column for sorting

Next, we repeat the process to create our numeric sort key.

1. Click Custom Column again.
2. Name the new column "Month-Year Sort."
3. Enter this M formula:

Date.ToText([Date], "yyyyMM")

Click OK. It's a good idea to change the data type of this column to "Whole Number" for optimal performance.

Step 3: Close, Apply, and Set the Sort Order

Once you’ve added both columns, click Close & Apply in the top-left corner of the Power Query Editor. This saves your changes and loads them into the data model.

Heads up: Even though you created the columns in Power Query, you must still do the "Sort by Column" step back in the Power BI Data View, just like we did in Method 1. This relationship isn't set in Power Query.

Go to the Data View, select your "Month-Year" column, and use the "Sort by Column" feature to link it to your "Month-Year Sort" column.

DAX vs. Power Query: Which Method Is Better?

Both methods achieve the same goal, so when should you use one over the other?

• Power Query (Recommended Best Practice): This method prepares your data before loading it into the model. This is part of the Extract, Transform, Load (ETL) process. It generally results in a more efficient data model because the columns are compressed and stored more effectively from the start. If you're building a report from scratch or have access to edit the data sources, use Power Query.
• DAX (Good for Quick Additions): Creating DAX calculated columns is often faster for a quick one-off addition. It's useful if you’ve already loaded your data or don’t have access to the underlying query. The trade-off is that calculated columns are less efficient in terms of memory and can slow down your refresh times on very large datasets compared to a Power Query transformation.

Practical Tips and Reminders

• Never Skip the Sort Column: This is the most common mistake. Your reports will be misleading if they are not sorted chronologically. Always create a helper column and apply the "Sort by Column" setting.
• Hide the Sort Column: Once you've set the sort order, you don't need to see the "Month-Year Sort" column in your report view. Right-click it in the Fields pane and select "Hide" to keep your model clean for end-users.
• Consider Your Audience: Choose a format that is universally understood. "Jan 2024" or "January 2024" is usually safer than ambiguous formats like "01/24".

Final Thoughts

Combining and correctly sorting month and year in Power BI is a fundamental skill for building clear, professional-looking reports. By using either a DAX calculated column or a Power Query custom column, along with the all-important helper column for sorting, you can present time-based data in an intuitive and chronologically accurate way.

Navigating steps like these - writing formulas, creating helper columns, and configuring sort-by settings - is standard in traditional BI tools, but we believe getting insights shouldn't require so much tedious setup. With Graphed, you simply connect your data and ask questions in plain English, like "show me website traffic by month for the last year." We handle the data shaping, formatting, and chronological sorting for you, instantly generating a clean visualization so you can skip the manual setup and get a clear, accurate chart immediately.