How to Extract Date from Datetime in Power BI
How to Extract Date from Datetime in Power BI, When working with datetime values in Power BI, it is often essential to isolate the date component for analysis, reporting, and visualization.
Using DAX (Data Analysis Expressions), you can easily extract the date from a datetime column.
How to Extract Date from Datetime in Power BI
In this article, we will guide you through the process of doing this efficiently using the FORMAT function in DAX.
What is DAX?
DAX, or Data Analysis Expressions, is a powerful formula language used in Microsoft Power BI, Excel, and other Microsoft applications.
DAX allows users to perform complex calculations and create data analysis queries to make data insights clearer and more actionable.
Extracting Date from Datetime in Power BI
To extract the date from a datetime column in Power BI, you can utilize the FORMAT function.
This function helps you convert datetime values into date formats that are easier to read and analyze.
Step-by-Step Example
Let’s say you are working with a table named my_data that contains sales information recorded at specific datetimes.
This table has a column called Datetime that includes both the date and time of each sale.
Follow These Steps to Extract the Date:
Step 1: Open Your Power BI Project
Launch Power BI and navigate to your project that contains the my_data table.
Step 2: Create a New Column
- Click on the Table Tools Tab: On the top ribbon, click on the Table Tools tab.
- Select New Column: Choose the New Column icon to initiate the creation of a calculated column.

Step 3: Input the DAX Formula
In the formula bar, enter the following DAX expression:
date = FORMAT('my_data'[Datetime], "M/D/YYYY")
Explanation of the Formula
- date: This is the name of the new column you are creating to store the extracted dates.
- FORMAT: This function specifies how to format the datetime value.
- ‘my_data'[Datetime]: This refers to the Datetime column within the my_data table.
- “M/D/YYYY”: This is the format in which you want the date to appear, such as month/day/year.
Step 4: Validate the New Column
Upon pressing Enter to execute the formula, Power BI will generate a new column named date. This column will only display the date extracted from the corresponding datetime in the original Datetime column.
Example Outputs
After implementing the formula, here’s how the output would look:
- The formula extracts 1/1/2024 from 1/1/2024 10:15:00 AM.
- It retrieves 1/5/2024 from 1/5/2024 7:15:23 PM.
- It extracts 2/15/2024 from 2/15/2024 1:15:09 AM.
This ensures clarity and simplicity in your data analysis.
Additional Information
For further details about the FORMAT function and its various capabilities, you can refer to the official Microsoft documentation.
Conclusion
Extracting the date from a datetime column in Power BI using DAX is a straightforward process that enhances your data analysis capabilities. By understanding how to use the FORMAT function, you can create clearer, more actionable insights from your data, whether you’re analyzing sales trends or managing project timelines.