How to format dates in Power Bi tables and dashboards?

Question:
Our HR team is struggling with inconsistent date formats across our employee reports which we manage using Power BI. Some dates show as MM/DD/YYYY, others as DD/MM/YYYY, and many include unwanted time stamps. How can we standardize these formats for better readability ?

Understanding Date Formatting in Power BI

In this post, we will explore both Power Query (for data load formatting) and DAX (for visualization formatting) approaches, focusing on practical scenarios like employee hire dates and review schedules.

Data Model:

In this example – we will use the following data model:
HR_Records table containing EmployeeID (text), HireDate (datetime), LastReviewDate (datetime), NextReviewDate (datetime), AttendanceDate (datetime), ShiftStartTime (datetime), ShiftEndTime (datetime).

Using Power Query (Recommended)

To change the data format in Power BI tables, proceed as following:

1. Open Power Query Editor:

• Click “Home” > “Transform data”
• Select your query – in our case its name is: HR_Records.
• Click on the date column you want to format.

2. In the “Transform” tab of the ribbon, click on the “Data Type” dropdown and make sure that the Data Type value for the column is set to “Date” or “Date/Time”.
3. Click ‘Add Column’ in the ribbon
4. Click ‘Custom Column’
5. For Date Formatting:

• Click ‘Add Column’ in the ribbon
• Click ‘Custom Column’
• Use the following code

// For HireDate formatting
New Column Name: Formatted_HireDate
Custom Column Formula: Date.ToText([HireDate], "MM/dd/yyyy")

// For LastReviewDate formatting
New Column Name: Formatted_LastReviewDate
Custom Column Formula: Date.ToText([LastReviewDate], "MM/dd/yyyy")

• Ensure that the Data Type of the new column is Data (using the Transform ribbon).

6. For Time Formatting:

• Follow same steps as above and use the following Code.

// For ShiftStartTime formatting
New Column Name: Formatted_ShiftStart
Custom Column Formula: Time.ToText(Time.From([ShiftStartTime]), "hh:mm AM/PM")

// For ShiftEndTime formatting
New Column Name: Formatted_ShiftEnd
Custom Column Formula: Time.ToText(Time.From([ShiftEndTime]), "hh:mm AM/PM")

Common Date/Time Format Patterns

// Date Formats
"MM/dd/yyyy"     // Example: 03/15/2024
"dd-MMM-yyyy"    // Example: 15-Mar-2024
"MMMM dd, yyyy"  // Example: March 15, 2024
"yyyy-MM-dd"     // Example: 2024-03-15

// Time Formats
"HH:mm"          // Example: 14:30 (24-hour)
"hh:mm AM/PM"    // Example: 02:30 PM
"HH:mm:ss"       // Example: 14:30:45

Date formatting use DAX (Post-load)

1. Create the following formatted date columns:

Formatted_Hire_Date = 
FORMAT('HR_Records'[HireDate], "mmmm dd, yyyy")

Review_Quarter = 
FORMAT('HR_Records'[LastReviewDate], "Q") & " " & 
FORMAT('HR_Records'[LastReviewDate], "yyyy")

2. Create dynamic date measures:

Days_Until_Review = 
VAR CurrentDate = TODAY()
RETURN
DATEDIFF(
    CurrentDate,
    'HR_Records'[NextReviewDate],
    DAY
)

Real-world HR Scenario: Employee Review Dashboard

Let’s create a review tracking system:

1. Create a review status measure:

Review_Status = 
VAR DaysToReview = [Days_Until_Review]
RETURN
SWITCH(
    TRUE(),
    DaysToReview < 0, "Overdue",
    DaysToReview <= 30, "Due Soon",
    "On Track"
)

2. Format review dates in a matrix:

• Rows: EmployeeID
• Columns: Last Review Date, Next Review Date
• Values: Review Status
• Apply conditional formatting using the Review_Status measure

Common errors and Troubleshooting

1. Dates Appearing as Numbers:

// In Power Query
= Table.TransformColumns(
    HR_Records,
    {{"HireDate", type date}},
    Date.From
)

2. Inconsistent Regional Formats:

• Check File > Options > Regional Settings
• Ensure consistency with your organization’s standards

2. Unwanted Timestamps:

Clean_Date = 
FORMAT(
    'HR_Records'[HireDate],
    "mm/dd/yyyy"
)

4. Blank Date Values:

Safe_Date = 
COALESCE(
    'HR_Records'[HireDate],
    LASTDATE('HR_Records'[HireDate])
)