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.

Real-world HR Scenario:

Imagine you’re creating a report on employee tenure. You have hire dates ranging from 1990 to 2024, and you want to display them as “Quarter Year” (e.g., “Q1 2020”). Here’s how to do it:

  1. Create a new measure:
   Hire Quarter = 
   FORMAT('EmployeeTable'[HireDate], "Q") & " " & 
   FORMAT('EmployeeTable'[HireDate], "yyyy")
  1. Use this measure in a matrix or table visual to show employee counts by hire quarter.

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.