When accessing data from other data sources, It is inevitable that we will encounter dates in formats that are not as desired. Therefore, how do we change the format of the dates to the desired format? Power BI Date Format has a query for the locale date format that allows us to change the date format in the local date format.
You must be aware that there are numerous ways to manipulate Power BI Date Format. But, I know that you will have confusions about how to change date format in Power BI. In this blog, we will demonstrate various methods to change date format in Power BI to help you with all that. Let’s get started!
What is Power BI?
Power BI is a proprietary Data Visualization and Business Intelligence platform from Microsoft Power Platform. It is one of the most used business tools for analyzing and reporting on company data. Power BI seamlessly connects to data, delivering Immersive Visuals, Interactive Reports, and Insights via a variety of built-in software services, apps, and connectors.
Connecting to a number of data source connectors in Power BI allows you to easily import data from the data source and view data stored in databases or data warehouses.
Hevo seamlessly integrates and transforms data from various sources to ensure your data is always analysis-ready. Hevo offers:
- A no-code data pipeline with a drag-and-drop interface.
- Pre-load and post-load transformation capabilities.
- End-to-end encryption to ensure data safety and completeness.
- A cost-efficient solution for all your data integration needs.
Hevo makes data transfers effortless, giving you more time to focus on insights.
Get Started with Hevo for Free
How Power BI Format Dates?
Before you format dates in Power BI, make sure that Power BI Desktop is set to the relevant region. To examine and alter your regional settings, go to the Power BI dashboard and select:
File – > Options & Settings –> Options.
There are several ways to format dates in Power BI, and we will describe the majority of them. We’ll utilize the SQL table that we imported in the enter data article to show various Power BI date types.
When you select the Edit Queries option, a new window called Power Query Editor appears.
The HireDate field can be seen in the screenshot above. This column will be used to demonstrate the Power BI date formatting choices.
1) The First Approach to Power BI Date Format
Please pick the Date Column and open the context menu by right-clicking on it. Choose the Transform option, then the Formatting option. For the time being, we will select the Year. This property extracts and shows the Years from the Hire date.
The date and time in the Hire date column have been substituted by the Extracted Year in the example below.
2) The Second Approach to Power BI Format
To begin with, the Power BI Date Format, navigate to the Transform Tab. The Date and Time choices are available under this tab. You can format the dates using these drop-down options.
The drop-down list of different data formats is displayed when you click the down arrow beside the Date. We’re going to use Days in Month as an example.
The date and time in the Hire date column have been changed by the total number of days in a month in the example below.
3) The Third Approach to Power BI Format
The drop-down list of different data formats is displayed when you click the down arrow beside the Date. We’re going to use the Days in Month example. Follow a simple step-by-step instruction below!
- The date and time in the Hire date column have been changed by the total number of days in a month in the example below.
- In Power BI, let me select the Date Only option.
- You may now see the new (extra) column displaying the date from the Hire date.
- Select Time Only
- You may now see the new column indicating the Time since the Hire date.
- Choose the day’s name.
- The extra column displaying the day’s name is now visible. Then, under the Home tab, select the Close & Apply option to save your changes.
- Please wait as the changes take effect.
Under the SQL Employee table, you can now view the Formatted Date, Day Name, and Time columns.
Things to Remember in Power BI Date Format
Here are a few things you should “know” before diving into Power BI Date:
- The date format is comparable to that of Microsoft Excel.
- In Power BI, we have predefined data formats.
- By default, if the system date format in the United States is used, Power BI will use the same date format.
Limitations of Power BI Date Format
The Limitations of Power BI Format Date are mentioned below:
- Relationships created on the DateTime columns may not operate as expected when using Analysis Services data sources.
- Date and Time data types are Power BI formatting elements implemented on top of Analysis Services, therefore any model-dependent objects will still look as DateTime to Analysis Services.
- As a result, even if a user selects Date from the Modeling tab for such columns, they do not register as the same date since Analysis Services still considers the time element of the data.
Conclusion
The first step in formatting dates is to confirm that you are using the proper locale for your date settings for your Power BI Date Format.
If changing the regional locale settings does not fix your problem, you can format dates in the Power BI reports view. Finally, if you need to extract sections of dates or if your date formats are inconsistent, you can use the Power BI Query editor to gain greater fine-grained control over date forms.
FAQ
How do I change the format of the date in Power BI?
To change the format of the date in Power BI, select the date column in the Data view or Report view, then go to the Modeling tab and choose the desired format from the Format dropdown.
How to change date format from dd mm yyyy to mm dd yyyy in Power BI?
To change the date format from dd mm yyyy to mm dd yyyy in Power BI, select the date column, go to the Modeling tab, and customize the format using a new custom format like MM/DD/YYYY.
How to write date in Power BI?
To write a date in Power BI, use a calculated column or measure with the DATE() function, specifying the year, month, and day as arguments: DATE(2024, 9, 11).
Share your knowledge on Power BI Date Format in the comment section below! We would love to hear your thoughts.
Davor DSouza is a data analyst with a passion for using data to solve real-world problems. His experience with data integration and infrastructure, combined with his Master's in Machine Learning, equips him to bridge the gap between theory and practical application. He enjoys diving deep into data and emerging with clear and actionable insights.
