How to Get First 3 Letters of Month in Power BI

Building a report in Power BI often means getting your dates to look just right. If you need to display months as three-letter abbreviations like 'Jan', 'Feb', and 'Mar' for cleaner charts and tables, you're in the right place. This guide will walk you through several methods to achieve this, from quick formatting fixes to more permanent solutions using Power Query and DAX.

The Quickest Method: Using Visual Formatting Options

Sometimes, all you need is a quick change for a specific chart or table. If you already have a date column and you just want to change how it's displayed in one place, Power BI's built-in formatting tools are your best friend.

This approach works directly on the visual and doesn't alter your underlying data model. It's perfect for a one-off adjustment.

How to Format a Date Axis

Let's say you have a bar chart showing sales over time and the X-axis is displaying the full month name ("January", "February"). Here's how to shorten it:

1. Select the visual you want to change.
2. In the Visualizations pane on the right, click the paintbrush icon to open the Format visual options.
3. Expand the settings for the relevant axis (e.g., X-axis for a column chart or Y-axis for a bar chart).
4. Under the Values section, you should see a Date Format/Type field. It might be set to "Auto" or a specific format like "MMMM YYYY".
5. Click the dropdown or enter a custom format. To get the three-letter month, type in MMM or MMM 'YY if you want to include a shorthand year.

The axis on your visual will instantly update to show the abbreviated month names. It's that simple!

A Quick Reference for Date Format Codes

The "MMM" code is part of a standard set of date formatting symbols. Here are a few others you'll find useful:

• d: The day of the month (e.g., 5).
• dd: The day of the month with a leading zero (e.g., 05).
• ddd: The abbreviated day of the week (e.g., Fri).
• dddd: The full day of the week (e.g., Friday).
• M: The month number (e.g., 9).
• MM: The month number with a leading zero (e.g., 09).
• MMM: The three-letter abbreviation of the month (e.g., Sep).
• MMMM: The full name of the month (e.g., September).
• yy: The two-digit year (e.g., 24).
• yyyy: The four-digit year (e.g., 2024).

You can combine these to create custom formats like "MMM-dd-yyyy" (Sep-05-2024) or "ddd, MMMM d" (Thu, September 5).

Why Quick Formatting Isn't Always the Answer

While visual formatting is fast, it has its limits. The change only applies to that one specific visual. The underlying data in your table is still a full date. This causes problems if you want to:

• Use abbreviated month names in a Slicer.
• Show abbreviated months in a data Table visual.
• Group data by the three-letter month name in calculations.

For these situations, you need a more robust solution that creates a new column in your data model. That's where Power Query and DAX come in.

The Power Query Method: Transforming Your Data at the Source

Power Query is Power BI's built-in tool for extracting, transforming, and loading data. Creating your month abbreviation here is considered a best practice because the transformation happens before the data is loaded into your model. This generally leads to better performance and a cleaner dataset.

Step-by-Step Guide in Power Query

Follow these steps to add a dedicated "Month Abbreviation" column.

1. From Power BI Desktop, click on Transform data on the Home ribbon. This will open the Power Query Editor.
2. In the Queries pane on the left, select the table that contains your date column.
3. Select the date column you want to work with (e.g., OrderDate).
4. Go to the Add Column tab in the ribbon at the top.
5. Click the Date button, then hover over Month, and finally click on Name of Month. This will add a new column with the full month name ("January", "February", etc.).
6. Now, select this new "Month Name" column.
7. In the Add Column ribbon, click the Extract button and select First Characters.
8. A dialog box will pop up. Enter the number 3 and click OK. Power Query will create another new column containing just the first three letters of the month.
9. Double-click the header of this new column to rename it to something meaningful, like Month Abbr. You can also delete the intermediate "Month Name" column if you no longer need it.
10. Once you're done, go to the Home tab and click Close & Apply to load the changes into your Power BI model.

You now have a clean, reusable column with the three-letter month abbreviations ready to use in any visual, slicer, or table!

Faster Alternative: Using a Custom Column in Power Query

If you're comfortable with writing simple formulas, you can skip several steps by using the "Custom Column" feature.

1. In the Power Query Editor, go to the Add Column tab and click Custom Column.
2. In the pop-up window, give your new column a name (e.g., Month Abbr).
3. In the formula box, type the following M code formula (remember to replace [YourDateColumn] with the actual name of your date column):

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

This single function tells Power Query to convert the date into text using the "MMM" format. Click OK, and you're all set! It's much faster and achieves the same result.

The DAX Method: Creating a Calculated Column

Another powerful way to add a month abbreviation column is by using DAX (Data Analysis Expressions). This is the formula language used by Power BI. Unlike Power Query, DAX calculated columns are created after the data is loaded into the model.

This is a great option if you're already working within the Power BI Desktop report view or if your logic depends on other DAX measures or columns.

1. In Power BI Desktop, navigate to the Data view by clicking the table icon in the left-hand navigation pane.
2. Select the table you want to add the column to from the Fields pane on the right.
3. Go to the Column tools ribbon that appears at the top and click on New column.
4. The formula bar will appear. Enter the following DAX formula, again replacing <YourTable>[YourDateColumn] with the correct table and column name:

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

Press Enter, and Power BI will instantly create a new column populated with the three-letter month abbreviations.

DAX vs. Power Query: Which Should You Choose?

For a simple transformation like creating a month abbreviation, the Power Query method is generally recommended. It pre-processes the data, which can make your report refresh more efficient and keeps your DAX model less cluttered with extra columns.

Use DAX when your calculation needs to be more dynamic or relies on relationships and other measures that only exist inside the data model.

The Final, Crucial Step: Sorting Your Months Chronologically

You've created your "Month Abbr" column, and you proudly add it to a chart... only to see it sorted alphabetically: Apr, Aug, Dec, Feb, Jan. This is a classic Power BI "gotcha," but the fix is easy.

To sort your months correctly, you need another column that represents the month number (1 for January, 2 for February, etc.).

1. Create a Month Number Column: You can create this easily in either Power Query (Add Column -> Date -> Month -> Month) or DAX (Month Number = MONTH(YourTable[YourDateColumn])). It's best to create this at the same time you create your abbreviation column.
2. Select Your Abbreviation Column: In the Data view in Power BI, click to select your Month Abbr column header.
3. Use the "Sort by column" Tool: With the Month Abbr column still selected, go to the Column tools ribbon at the top.
4. Click Sort by column and choose your Month Number column from the dropdown list.

That's it! It might take a moment, but Power BI will now understand the proper chronological order for your month abbreviations. Any visuals using that column will automatically update and sort correctly: Jan, Feb, Mar, Apr, and so on.

Final Thoughts

Now you know several ways to display three-letter month abbreviations in Power BI, from simple visual formatting to more robust solutions in Power Query and DAX. Most importantly, you know how to conquer the final hurdle of sorting those months correctly, ensuring your reports are not only clean but also accurate.

We built Graphed to remove the manual steps and learning curves often found in traditional BI tools. Instead of needing to know the difference between Power Query and DAX or memorizing format codes, you can simply ask, "Show me my sales by month this year as a bar chart" in plain English. We instantly generate the right chart for you - already properly formatted and sorted - by connecting directly to your data sources like Shopify, Google Analytics, or Salesforce.