How to Remove Day from Date in Power BI

Working with dates in Power BI can sometimes feel like trying to solve a puzzle with one hand tied behind your back. You know the insight you want to visualize - a clean month-over-month trend or a simple yearly summary - but your date column insists on showing the day, cluttering up your charts and tables. Fortunately, removing the day to display only the month and year is a common task with several straightforward solutions. This guide will walk you through a few different methods, from a quick format change to more powerful options using Power Query and DAX.

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 Remove the Day from a Date?

Before we jump into the "how," let's briefly touch on the "why." You'll often want to transform your dates for a few key reasons, especially in marketing and sales reporting:

• Data Aggregation: When you're analyzing trends over time, daily fluctuations can be noisy. Aggregating data by month and year (e.g., "January 2024," "February 2024") gives you a clearer view of performance for metrics like website traffic, monthly recurring revenue, or sales pipeline growth.
• Improved Visualization: A chart axis with dozens of daily dates like "01/01/2024," "02/01/2024," etc., is impossible to read. Shortening this to "Jan 2024," "Feb 2024" makes your bar charts, line graphs, and slicers much cleaner and easier for your audience to interpret.
• Relationship Building: In some data models, you may need a month-year key to create relationships between different tables (like a sales table and a marketing goals table) that don't have a common daily date.

Method 1: The Quickest Fix using the Column Tools Formatting Trick

If you just need to change how the date looks in your reports and charts without actually altering the underlying data, this is the fastest way to get it done. This method doesn't add a new column, it simply changes the display format of your existing date column.

This is perfect for when you want one visual to show the full date, but another visual on the same page to show only the month and year.

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-by-Step Instructions:

1. Navigate to the Report View in Power BI (the icon that looks like a bar chart on the left-hand navigation pane).
2. In the Data pane on the right side of the screen, find your table and click on the date column you want to format.
3. With the column selected, a new menu tab called Column tools will appear at the top of the ribbon. Click on it.
4. In the "Formatting" section of the ribbon, you'll see a "Format" dropdown menu. It will probably default to something like "Short Date" or have a specific format like m/d/yyyy.
5. Click the dropdown menu and select a format that only includes the month and year. The most common choice is "MMMM yyyy" (which will display as "January 2024") or "MMM yyyy" (which will display as "Jan 2024").

That's it! All visuals using this column will now display the date in your selected month-year format. Remember, the underlying data still includes the day, which is useful for maintaining Power BI's built-in date hierarchy functionality.

Method 2: Create a Permanent Month-Year Column with Power Query

If you need a new, dedicated column that contains only the month and year, Power Query is the best tool for the job. This method is considered a best practice because you're cleaning and transforming your data at a foundational level, before it even loads into your Power BI data model. This keeps your model organized and efficient.

We'll look at two ways to do this within Power Query: one using the user interface and another using a simple custom formula.

Option 2A: The No-Code Power Query Method

This approach uses the built-in transformations in the Power Query Editor menu, making it easy for users who aren't yet comfortable writing M code formulas.

Step-by-Step Instructions:

1. From the Home tab in Power BI Desktop, click Transform data. This will open the Power Query Editor.
2. Find and select the date column you want to work with.
3. With the column selected, navigate to the Add Column tab in the ribbon.
4. In the "From Date & Time" section, click on the Date button. A dropdown menu will appear.
5. From this menu, select Month > Start of Month. This creates a new column where every date is converted to the first day of its respective month (e.g., "15/01/2024" becomes "01/01/2024").
6. Now, select this newly created Start of Month column. From the Transform tab in the ribbon, go to Data Type and change it from "Date" to "Text". Power BI will create the conversion step.
7. Lastly, you'll apply the final formatting using the Column Tools trick from Method 1 once you're back in the main Power BI report view. Just select your new column and choose the "MMMM yyyy" format. You can also rename the column to something intuitive like "Month Year."
8. When you're happy with the changes, click Close & Apply in the upper-left corner of 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

Option 2B: The Custom M Code Power Query Method

For more direct control, you can create a custom column in Power Query using a one-line M code formula. Don't let the word "code" scare you, it's extremely simple and efficient.

Step-by-Step Instructions:

1. In the Power Query Editor, with your date column visible, go to the Add Column tab and click Custom Column.
2. A dialog box will appear. In the New column name field, give your new column a name, like "Month Year".
3. In the Custom column formula box, type the following formula. Make sure to replace [YourDateColumn] with the actual name of your date column. The square brackets are important!

= Date.ToText([YourDateColumn], "MMMM yyyy")

1. The formula checker below the box should say "No syntax errors have been detected." Click OK.
2. A brand new text column will appear with your dates perfectly formatted. You can then change the Data Type of this new column from "Any" to "Text" if needed.
3. Click Close & Apply.

This method is fantastic because it's a single, clean step that gives you the exact output you want directly within the Power Query process.

Method 3: Maximum Flexibility with a Calculated Column using DAX

DAX (Data Analysis Expressions) is Power BI's formula language for creating calculations and measures within your data model. You can use DAX to create a new "calculated column" directly in the Report or Data view, without having to open the Power Query editor.

This method is useful when you need to create a month-year column on the fly or if the logic is based on other DAX measures you’ve already created.

Step-by-Step Instructions:

1. Go to the Data view (the grid icon on the left navigation) or stay in the Report View.
2. In the Data pane on the right, find and right-click on the table containing your date column, then select New column.
3. The formula bar will appear at the top of the window. Type the following DAX formula, replacing 'YourTable'[YourDateColumn] with the proper name of your table and date column.

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

1. Press Enter. Power BI will instantly process the formula and add a new column to your table with the formatted month and year text.

While quick and easy, keep in mind that calculated columns created with DAX are processed when the data refreshes and consume more memory than custom columns created in Power Query. For performance reasons, it's generally recommended to do transformations in Power Query (Method 2) when possible.

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

Which Method is Right for You?

With three solid options, which one should you choose? Here's a simple breakdown:

• For the quickest visual change: Use Method 1 (Column Tools Formatting). It’s perfect when you don't need a separate column and just want to clean up your visuals.
• For the most robust and efficient solution: Use Method 2 (Power Query). This is the industry best practice for preparing your data model correctly.
• For on-the-fly calculations and flexibility: Use Method 3 (DAX Calculated Column). It's great when you can't or don't want to go back into the Power Query Editor.

Final Thoughts

Dealing with date formatting is a classic data analysis task, and mastering it in Power BI unlocks cleaner, more professional-looking reports. Whether you use the simple formatting tools on the ribbon, the power of a custom column in Power Query, or the flexibility of a DAX formula, you now have the tools to show your data exactly how your audience needs to see it.

We know that learning DAX or navigating Power Query is often just one small hurdle in the long, manual process of building marketing and sales reports. At Graphed, we believe getting insights shouldn't require you to become a data expert. We built Graphed to cut through that complexity, allowing you to connect sources like Google Analytics, Shopify, and Salesforce and then build dashboards in seconds just by asking questions. Instead of wrestling with formatting, you can just ask, "Show me my sales by month and year," and get a live, interactive chart instantly.