Graphed LogoGraphed
Login

How to Show Month and Year in Power BI

Cody Schneider•

Showing just the month and year in a Power BI visual seems like it should be simple, but it can be surprisingly tricky. By default, Power BI’s powerful date intelligence often breaks things down into a full hierarchy of year, quarter, month, and day. This article guides you through the common and reliable methods to get the clean “Month Year” format you want on your charts and tables.

Why Is Displaying Month and Year a Common Problem?

Power BI is designed to 'help' by automatically creating a date hierarchy whenever you add a date field to a report. This is great for drilling down into granular detail, but it often clutters your visuals when you just want to see a high-level trend, like monthly revenue over the past year. Your goal is usually a clean axis label like "Jan 2024," "Feb 2024," and so on.

The second part of the problem is sorting. If you create a simple text field for "Jan 2024," Power BI will, by default, sort it alphabetically - putting "Apr 2024" before "Feb 2024." Getting your months to appear in the correct chronological order is a crucial final step. Let's cover the solutions, starting with the most popular method.

Method 1: Create a Formatted Column with DAX

The most flexible and robust way to handle date formatting is by using Data Analysis Expressions (DAX) to create a new column in your date table. This gives you complete control over how the text is displayed and, importantly, how it's sorted.

For this method, it's a best practice to have a dedicated Date Table in your data model. If you don't have one, you can easily create one using the CALENDARAUTO() or CALENDAR() functions. Assuming you have a table named 'Date' with a column called '[Date]'...

Step 1: Create the 'Month Year' Text Column

First, navigate to the Data View in Power BI by clicking the table icon on the far left. Select your date table from the Fields pane on the right. Then, from the "Table tools" or "Column tools" ribbon at the top, click on New column.

This opens up the formula bar. Enter the following DAX formula:

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

Let’s break that down:

  • Month Year = This is the name of your new column.

  • FORMAT(...) This is the DAX function that converts a value (like a date) into a text string based on a specified format.

  • 'Date'[Date] This is the date column you are referencing.

  • "MMM yyyy" This is the format code. "MMM" gives the three-letter month abbreviation (Jan, Feb), and "yyyy" gives the full four-digit year (2024).

After pressing Enter, you'll see a new column in your table with values like "Jan 2024", "Feb 2024", etc.

You can customize the format string to get different looks:

  • "MMMM yyyy" will give you "January 2024" (the full month name).

  • "MMM-yy" will give you "Jan-24" (a two-digit year).

  • "M/yyyy" will give you "1/2024".

Step 2: Create a 'Sort Order' Column

If you add your new 'Month Year' column to a chart axis right now, you'll notice it sorts alphabetically. January 2024 will appear right after December 2023, but April 2024 will show up before February 2024. Not good.

To fix this, you need to create a second, helper column that Power BI can use to get the order right. While still in the Data View with your date table selected, click New column again and enter this formula:

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

This formula creates a numeric-like text string such as “202401”, “202402”, etc. Since these values are sequential, Power BI can easily sort them chronologically. An alternative that also works great and creates a whole number is:

Month Year Sort = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])

This formula achieves the same goal, creating numerical values like 202401, 202402, and so on. Both work perfectly.

Step 3: Apply the Sort by Column Setting

Now, you need to tell Power BI to use your new 'Sort' column to order your 'Month Year' column.

  1. Click to select the 'Month Year' column in the Fields pane (the one with the text, not the sort column).

  2. Go to the Column tools tab in the top ribbon.

  3. Click on the Sort by column dropdown menu.

  4. Select your helper column, 'Month Year Sort'.

That's it! Now, whenever you use the 'Month Year' column in any visual, it will automatically be sorted chronologically. This DAX method is a one-time setup that ensures consistency across your entire report.

Method 2: Manually Control the Date Hierarchy in a Visual

Sometimes you need a quick result and don't want to add new columns to your data model. You can manipulate Power BI’s auto date hierarchy directly within a visual.

  1. Add the Date Field: Drag your main date field from your date table onto the axis of a column or line chart.

  2. Observe the Hierarchy: You'll see that Power BI has broken it down into Year, Quarter, Month, and Day in the Chart's Axis well.

  3. Remove Unwanted Parts: To see just the Year and Month, simply click the "X" next to "Quarter" and "Day" in the axis well.

  4. Expand the View: The visual might still be "drilled up" to just show the year. Look for the drill-down icons in the top-right corner of the visual. Click the "Expand all down one level in the hierarchy" icon (it looks like a forked arrow pointing down).

Your chart will now display both the Year and the Month on the axis. It’s a very fast method for a quick analysis. The main downside is that the axis labels can be a bit long (e.g., showing both "2024" and "January"), and you have to repeat this process for any new visual you create.

Method 3: Using Power Query for Upstream Formatting

If you prefer to shape your data before it even loads into the Power BI data model, you can use the Power Query Editor. This is a great choice if you want to keep your DAX model clean or enforce this format for anyone using the dataset.

Step 1: Open Power Query and Select the Date Column

From the Home ribbon in Power BI Desktop, click on Transform data to open the Power Query Editor. Find your date table and select the primary date column.

Step 2: Add Custom Columns for Month & Year

Similar to the DAX method, we will create one column for display and one for sorting.

  1. With the date column selected, go to the Add Column tab.

  2. Click on Custom Column.

  3. Name the new column "Month Year" and enter the following M formula:

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

This does the exact same thing as the DAX FORMAT function, creating a text display column.

  1. Click OK. Now, create the sort column by clicking Custom Column again.

  2. Name the new column "Month Year Sort" and enter this formula:

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

Step 3: Close, Apply, and Sort

Once you have your two new columns, click Close & Apply in the Home tab of the Power Query Editor.

Your new columns will now appear in your data model. You will still need to perform the "Sort by column" step detailed in Method 1. Select the 'Month Year' column in the Data View, go to Column tools, and set it to sort by your 'Month Year Sort' column.

This approach is excellent because the data preparation happens during the refresh, making your Power BI file itself a tiny bit more efficient.

Which Method Should You Choose?

For most users, Method 1 (DAX) is the best choice. It's done directly within the Power BI interface, is incredibly flexible, and works universally across your whole report once you set it up. It aligns well with the standard practice of having a centralized date table that feeds all your time intelligence calculations.

Method 2 (Visual Hierarchy) is your best friend for quick, exploratory analysis. When you're just looking for a trend and don't want to modify the data model, it gets the job done in seconds.

Method 3 (Power Query) is for those who take a data-shaping-first approach. It's technically very sound and keeps your DAX model clean, which is preferable in complex enterprise-level models or when preparing a dataset to be shared with others.

Ultimately, by mastering the DAX format and sort column combination, you'll be well-equipped to handle nearly any date formatting challenge that Power BI throws at you.

Final Thoughts

This article covered the most effective ways to show the month and year in Power BI, addressing both the text formatting and the crucial chronological sorting. By adding a calculated display column and a helper sort column using either DAX or Power Query, you can achieve a clean, professional-looking visual every time.

Creating these kinds of reports often feels repetitive - pulling data, cleaning it, and manually building the right visualizations. We built Graphed to automate that entire process. You simply connect your data sources and describe the dashboard you need in plain English, and Graphed builds it in seconds, without you needing to write a single line of DAX or worry about sort-order columns again.