How to change column date formats in Power BI?

by

User Question:

I’m working on a Power BI report for our HR department, and the dates in our employee database are showing up in an unfamiliar format. How can I change the date format to make it more readable for our HR team?

Modify datetime formatting in Power BI

In this comprehensive tutorial, we will go through two methods to change date formats: using Power Query and DAX. We’ll start with the Power Query method, which is often preferred as it modifies the data during the loading process.

We will use the following data model: EmployeeTable, with the following fields: EmployeeID: Number, Name: Text, HireDate: Date, LastReviewDate: Date.

Method 1: Using Power Query

  1. Open your Power BI Desktop and go to “Home” > “Transform data” to open the Power Query Editor.
  2. In the Queries pane, select the query containing your date column (e.g., EmployeeTable).
  3. Click on the column you want to reformat (e.g., HireDate).
  4. In the “Transform” tab of the ribbon, click on the “Data Type” dropdown and ensure the column is set to “Date” or “Date/Time” as appropriate.
  5. For custom formats not available in the list, you can use a custom column:
    • Go to “Add Column” > “Custom Column”
    • Enter a name for your new column – for example: “Custom Hire Date”.
    • Use the following formula, adjusting the format as needed:
      = Date.ToText([HireDate], "dd-MMM-yyyy")
  6. Repeat these steps for other date columns if needed.
  7. Click “Close & Apply” in the “Home” tab to load the transformed data into your report.

Power Query M code for custom formatting (if you prefer to use the Advanced Editor):

= Table.AddColumn(#"Previous Step", "Formatted HireDate", each Date.ToText([HireDate], "dd-MMM-yyyy"), type text)

This step adds a new column named “Formatted HireDate” with the specified date format.

Method 2: Using DAX (Post-load formatting)

If you prefer to keep the original date format in your data model and only change the display format in your reports, you can use DAX as described below.

  1. Go to the “Data” view in the left sidebar.
  2. Select the column containing the dates you want to reformat (e.g., HireDate).
  3. In the “Modeling” tab, find the “Format” dropdown in the “Properties” section.
  4. Choose the desired date format from the list (e.g., “DD/MM/YYYY” or “MM/DD/YYYY”).
  5. If you need a custom format, select “Custom” and enter the format string:
   dd-mmm-yyyy

This will display dates as “15-Jan-2024”.

  1. Repeat for other date columns if needed (e.g., LastReviewDate).
  2. Switch to “Report” view to verify the new format in your visuals.

Using DAX for Custom Formatting:

For more control, you can create a calculated column using DAX:

Formatted HireDate = 
FORMAT('EmployeeTable'[HireDate], "dd-mmm-yyyy")

This creates a new column with the formatted date strings.

Custom Date format not working

  • If dates appear as numbers, ensure the column is recognized as a Date type in the data model.
  • For dates not changing format, check if they’re actually stored as text. Use the DAX function DATE() to convert them: DATEVALUE([DateColumn]).
  • If custom formats aren’t applying, verify you’re using the correct format specifiers (e.g., “yyyy” for four-digit year, not “YYYY”).
  • For inconsistent formatting across visuals, check the field formatting in each visual’s properties pane.
  • If Power Query changes aren’t reflected, ensure you’ve clicked “Close & Apply” after making changes in the Query Editor.

Additional Date Formatting Techniques

When your Power BI reports will be viewed across different regions or shared with international teams, consider implementing region-aware date formatting. Use DAX expressions like FORMAT([HireDate], "General Date") which automatically adapts to the user’s locale settings. For reports accessed via Power BI Service on mobile devices, shorter date formats (MM/DD or DD/MM) often display better than verbose formats.

Integrating with Excel

If your HR team frequently exports Power BI data to Excel for further analysis, be aware that custom text-formatted dates won’t function as true dates in Excel. To maintain date functionality during exports, consider creating dual columns: keep the original date column hidden for calculations and display a formatted text version. When using “Analyze in Excel,” the original date formats will be preserved, allowing pivot table date grouping to work correctly.