How to Extract Date from DateTime in Power BI

Working with dates in Power BI often means dealing with columns that contain both the date and the time, known as DateTime values. But for most reports, like tracking daily sales or website sessions, you only need the date part. Trying to group or filter by a DateTime field can lead to messy, unhelpful reports where every second of the day is treated as a unique point. This guide will walk you through the cleanest and most efficient ways to extract just the date from a DateTime column in Power BI, using both DAX and 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

Why Bother Extracting Just the Date?

Before we jump into the "how," let's quickly cover the "why." Separating the date from the time isn't just about making your reports look cleaner, it's fundamental to good data modeling and analysis. Here’s why it’s so important:

• Accurate Aggregation: If you want to see total sales for January 5th, you need to group all transactions from that day. If you use a DateTime column, Power BI will see '2023-01-05 09:15:22' and '2023-01-05 14:30:05' as completely different values, preventing you from summing them up correctly.
• Building Relationships: The foundation of a good Power BI model is relationships, especially the one to a dedicated Calendar Table (also called a Date Table). These tables are essential for time intelligence functions (like Year-to-Date calculations). To create a relationship to a Calendar table, you need a clean date column in your data, not a DateTime one.
• Simpler Slicers and Filters: A date-only column gives you clean, simple slicers and filters in your reports. Instead of users seeing a massive list of timestamps, they get a user-friendly calendar to pick a specific day or date range.
• Improved Performance: Working with whole numbers (which is how Power BI stores dates internally) is faster than working with decimals (how DateTime values are stored). For large datasets, this can result in a snappier, more responsive report.

Method 1: Using DAX to Create a Calculated Column

DAX (Data Analysis Expressions) is the formula language used in Power BI. You can use it to create "calculated columns," which are new columns you add to your table based on data that already exists. This approach is done directly in the main Power BI report view after your data has been loaded.

Creating a DAX column is a quick way to get the job done, especially if you're already deep into building your report.

How to Create a Calculated Column

From the "Report" or "Data" view in Power BI Desktop:

1. Select the table containing your DateTime column from the 'Fields' pane on the right.
2. From the toolbar at the top, select the "Table Tools" tab.
3. Click on "New Column".
4. A formula bar will appear where you can enter your DAX expression.

Now, let's look at a few different DAX formulas you can use.

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

Option A: Using the INT() Function

This is often the simplest and most performant DAX method. Behind the scenes, Power BI stores DateTime values as a number where the integer part represents the date and the decimal part represents the time. The INT() function simply strips away the decimal portion, leaving just the whole number for the date.

In the formula bar, type the following, replacing 'YourTable'[YourDateTimeColumn] with your actual table and column names:

Order Date = INT('YourTable'[OrderDateTime])

After you press Enter, a new "Order Date" column will appear. The final, critical step is to format this new column as a date. Select the new column, go to the "Column Tools" tab, and change the "Data type" to "Date" and adjust the "Format" to your preference (e.g., Short Date).

Option B: Using the DATE() Function

This method is more explicit and can be easier to read if you're new to DAX. It works by extracting the year, month, and day components from your DateTime column individually and then reassembling them into a pure date value.

Use this DAX formula:

Order Date = DATE( YEAR('YourTable'[OrderDateTime]), MONTH('YourTable'[OrderDateTime]), DAY('YourTable'[OrderDateTime]) )

This formula is very readable and leaves no doubt about what it's doing. Because it uses the DATE() function, Power BI automatically formats the result as a Date data type, saving you an extra step.

Option C: Using the FORMAT() Function (Use with Caution)

You can also use the FORMAT() function to display the date. However, this is generally not recommended because it converts your date into a text string, not a true date value.

Order Date = FORMAT('YourTable'[OrderDateTime], "MM/DD/YYYY")

While this column will look like a date, it loses all its date properties. You won't be able to sort it chronologically correctly (it will sort alphabetically), use it in time intelligence calculations, or connect it properly to a calendar table. Use this method only for display purposes in very specific situations, not for modeling.

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

The Power Query Editor is Power BI's data transformation engine. It's the place where you clean, shape, and prepare your data before it gets loaded into the data model. For tasks like extracting a date, using Power Query is considered a best practice because it cleans the data at the source, resulting in a leaner, more efficient data model.

How to Open the Power Query Editor

From the main Power BI Desktop window, go to the "Home" tab on the ribbon and click "Transform data." This will open the Power Query Editor in a new window.

Option A: Using the Built-in 'Date Only' Transformation (The Easiest Way)

This UI-driven approach requires no formulas and is perfect for beginners and experts alike.

1. In the Power Query Editor, find the table and the DateTime column you want to change.
2. It's good practice to not overwrite your original column. Right-click the column header and select "Duplicate Column". This preserves the original DateTime field in case you ever need it.
3. Select the new, duplicated column.
4. Go to the "Transform" tab in the ribbon.
5. In the "Date & Time Column" section, click the "Date" dropdown and then select "Date Only".

That's it! Power Query instantly converts the column to a date type and records the step in the "Applied Steps" pane on the right. This is my go-to recommendation for its simplicity and efficiency.

Option B: Using a Custom Column with an M Formula

If you prefer more control or want to understand what's happening under the hood, you can add a custom column using Power Query's M formula language. This achieves the same result as the UI method.

1. Select the table, then go to the "Add Column" tab in the Power Query ribbon. Click "Custom Column."
2. In the dialog box that appears, give your new column a name (e.g., "Order Date").
3. Enter the following M formula, replacing [YourDateTimeColumn] with the name of your DateTime column from the list of available columns on the right:

= DateTime.Date([YourDateTimeColumn])

1. Click "OK." A new column formatted as a date will appear. Always double-check that the data type icon next to the column header shows a calendar, not a calendar with a clock. If not, click the icon and change it to "Date."

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

DAX vs. Power Query: Which Method Should You Use?

Both methods will give you a clean date column, but they work in fundamentally different ways. Here’s a quick breakdown to help you decide:

Power Query (The Preferred Method for Transformation):

• Executes During Data Refresh: Transformations are applied before data is loaded into the model. This is the optimal place for data cleaning and preparation.
• Reduces Model Size: By converting the column upfront, the data model becomes leaner. Columns with fewer unique values (like 365 dates vs. millions of unique timestamps) compress much better.
• Better Performance: A clean and optimized model created in Power Query will perform better and refresh faster.
• Motto: Shape your data first, then load it.

DAX (Best for In-Model Calculations):

• Executes After Data is Loaded: Calculated columns exist within the data model itself and are computed row by row.
• Increases Model Size: DAX calculated columns are not compressed as effectively as native columns processed through Power Query, which can bloat your file size.
• Good for Dynamic Analysis: DAX is ideal when you need calculations that respond to user selections (as Measures), but for a static transformation like this, Power Query is superior.
• Motto: Use DAX for analysis, not for data prep, when possible.

The Verdict: Unless you have a specific reason not to, you should always try to perform your date extraction in the Power Query Editor. It sets you up for a more robust, scalable, and performant Power BI report.

Best Practices to Remember

• Always Check the Data Type: Whichever method you use, always confirm your new column's data type is set to "Date". An incorrect data type is the most common source of errors in Power BI.
• Use a Calendar Table: Once you have your clean date column, the next step in building a professional-grade report is to connect it to a dedicated calendar table. This unlocks the powerful time intelligence capabilities of DAX.
• Smart Naming Conventions: Rename your final column to something clear like "Purchase Date" or "Ship Date". This makes your data model intuitive for you and anyone else who might work with it.

Final Thoughts

Getting a clean date column from a DateTime field is a foundational skill in Power BI. As we've seen, you can achieve this through both DAX calculated columns or Power Query transformations. For data preparation, turning to the Power Query Editor is nearly always the better choice, as it helps create a more efficient and responsive data model that is easier to manage long-term.

While mastering tools like Power BI is incredibly rewarding, we know that getting bogged down in the intricacies of DAX or Power Query can take up valuable time you could be spending on strategy. We built Graphed to remove this friction. Instead of having to manually clean and model your data, you can connect your sources and just ask for what you want in plain English. A request like "Show me sales by day for the last two quarters" just works, because we handle all the tedious data prep - like extracting dates from timestamps - behind the scenes, delivering a live dashboard instantly.