Power BI Date Formatting

What is Date Format in Power BI?

Date Format in Power BI refers to displaying and formatting dates within your reports and visuals. It defines how dates are presented to users, including the arrangement of day, month, and year, the use of separators, and whether to include time information. Proper date formatting enhances the readability and user-friendliness of your reports.

How to Change Date Formatting in Power BI?

You can change date formatting in Power BI by following one of these highlighted steps:

Step 1: Import the dataset to Power BI Desktop and load the dataset using the Get data option in the Home tab.

Step 2: Navigate to the Fields pane once the dataset is loaded. Select the table, right-click on the table, and choose Edit Query from the menu.

It will open a Power Query Editor window.

Step 3: Select the date column you want to format.

Step 4: Navigate to the Home tab and choose Data Type as Date/Time.

It would display a pop-up window to confirm the Change Column Type.

Step 5: Select the Replace current option. You will notice the date format has changed to Date/Time data type.

Step 6: Click the Close and Apply option under the Home tab to save the changes and return to the Power BI Desktop screen.

Now, navigate to the Fields pane and select the table visual that contains the date column. Navigate to the Column tools tab and choose Format.

Step 7: Click on the dropdown next to the Format option. You will see the various Date formats supported in Power BI Desktop.

Step 8: Choose any Date formats from the Format dropdown menu.

Step 9: Navigate to Table view on the left navigation menu in Power BI Desktop. You will notice the format of the Date field has now changed to the chosen format.

• Custom Formatting

You can apply custom date formatting using DAX (Data Analysis Expressions) expressions in calculated columns or measures.

To apply custom formatting, follow the steps highlighted below:

Step 1: Navigate to the Fields pane. Select the table, right-click on the table to open the menu bar, and choose the New measure or New column option.

Step 2: Use the FORMAT function to enter the DAX expression in the formula bar. It would enable you to apply any customized date format to the data field you want to change date formatting.

FORMAT (Value, Format, [LocaleName])

• Value – Refers to the value or expression that’s evaluated
• Format – A string with formatting template
• LocaleName – Name of locale

Step 3: Click on the Commit icon to save the changes.

Step 4: Navigate to Table view on the left navigation menu in Power BI Desktop. You will notice the format of the Date field has now changed to the chosen format.

The next section will see how to change date formatting in Power BI through different examples.

Examples

In this section, we will demonstrate how to change Power BI date formatting in table and Power BI date formatting in visual in a step-by-step process with examples.

Example #1

In this example, we will change Power BI date formatting in a table to create additional columns in PowerQuery Editor using the Uber Request Data. Uber Request Data dataset contains the details of cabs booked, such as pickup point, driver details, status, request, and drop date with the timestamp.

To change Power BI date formatting in table, follow the steps highlighted below:

Step 1: Open the Power BI Desktop and import the Uber Request Data dataset using the Get Data option. Once you click on Get data, the system will prompt a pop-up window to choose the file. Select the file and click on the Open button to import the dataset.

Step 2: Once the data is imported, click on the Load button to load the dataset to Power BI Desktop.

Once the data is loaded, you can view it by navigating to the Fields pane.

Step 3:

• Navigate to the Fields pane.
• Right-click on the table.
• Choose the Edit Query option.

It will open a PowerQuery Editor window with a table dataset.

You can also view the various in-built data types supported in PowerQuery Editor for date and time fields.

Step 4: Navigate to the Add Column tab, select the column you want to use to create a new column, and then choose the option from the Date menu.

Here we want to create a new column from the Request timestamp. We have selected the Date Only option to create a date field.

Rename the newly created column to a meaningful description by right-clicking on the newly created column. Here we have renamed the new column to Request Date.

Step 5: Select the column and choose an option from the Time menu. It will create a new column with Time details.

Rename the column to a meaningful name by choosing the Rename option when you right-click on the new column. Here we have renamed the new column to Request Time.

Step 6: Repeat Steps 4 and 5 above to create additional columns based on the Drop timestamp column. Here, we have created two columns, i.e., Drop Date and Drop Time.

Step 7: Navigate to the Home tab and choose the Close & Apply option to save the changes.

Once the changes are saved, the PowerQuery Editor will be closed and you will be navigated to the Power BI Desktop.

Step 8: Navigate to the Fields pane to view the new columns.

You can also view the data for the new columns by navigating to the Table view on the left navigation menu.

Example #2

In this example, we will apply Power BI date formatting in visual to change the date formatting for the existing date field using the INDEX_US_S&P US_SPX. INDEX_US_S&P US_SPX dataset contains S&P price fluctuations such as Open, Close, Low, High, and Daily returns data.

To change Power BI date formatting in the table, follow the steps highlighted below:

Step 1:

• Open the Power BI Desktop.
• Import the INDEX_US_S&P US_SPX dataset using the Get Data option.
• Click on the Load button.

Once the data is loaded, you can view it by navigating to the Fields pane.

Step 2: Select the column you want to change date formatting. It would enable two additional tabs, i.e., Table tools and Column tools. You will notice Date-related headers such as Data type and Format option under Column tools.

Step 3: Click on the Format dropdown. You will see all the built-in formats for date field you can choose for the date formatting.

Choose a date format from the options under the Date formats label.

Step 4: Navigate to the Table view and select the table. You will notice that the Date field has been formatted per the option you selected in the previous step.

Step 5: Navigate to Column tools and change the Data type to Date/time.

Step 6: Click on the Format dropdown. You will see different formatting options under Common formats, Date formats, and Date/time formats labels.

Step 7: Select a formatting option from the Format dropdown.

You will notice the Date column has been formatted to the option you have selected in the previous step.

Important Things to Note

• Ensure that the column you’re formatting as a date is recognized as a Date data type in Power BI. If not, the formatting options may not work as expected.
• Consider time zone considerations, especially if your data uses UTC (Coordinated Universal Time). Adjust the time zone settings in Power BI to display dates in the desired time zone.
• After making date format changes, refresh your data to see the updated formatting in your reports.
• Power BI allows for custom date formatting using format strings. Familiarize yourself with date format strings (e.g., “yyyy-MM-dd”) to create custom date formats.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to What Is Power BI Date Formatting. We learn the ways to modify date format on both table & visuals of existing data fields. You can learn more from the following articles –

Power BI Career

SUMX in Power BI