How to convert datetimes in UTC to Local Time in Power BI?

by

Question

I have an HR attendance dataset with UTC timestamps, but I need to display local times in my Power BI reports. How can I convert UTC to local time for accurate reporting across different time zones? Is there a way to handle daylight saving time changes automatically?

Introduction

Power BI offers robust solutions to handle this conversion, including automatic adjustments for daylight saving time. Let’s explore how to implement this using a single-table approach.

Assumed Data Model

We’ll define a table named EmployeeActivities. The table will contain the following columns:
Columns: EmployeeID, ActivityTimestamp UTC, ActivityType, TimeZone

Step-by-step Implementation

  1. In Power BI, we will use the Modeling tab to create a new measure for local time conversion, using the following DAX:
Local Time = 
VAR CurrentTimezone = SELECTEDVALUE(EmployeeActivities[TimeZone], "UTC")
RETURN
    SWITCH(
        CurrentTimezone,
        "EST", EmployeeActivities[ActivityTimestampUTC] - TIME(5,0,0),
        "PST", EmployeeActivities[ActivityTimestampUTC] - TIME(8,0,0),
        "UTC", EmployeeActivities[ActivityTimestampUTC]
    )
  1. The we can also create a calculated column for a more permanent solution:
Local Time Column = 
VAR CurrentTimezone = EmployeeActivities[TimeZone]
RETURN
    SWITCH(
        CurrentTimezone,
        "EST", EmployeeActivities[ActivityTimestampUTC] - TIME(5,0,0),
        "PST", EmployeeActivities[ActivityTimestampUTC] - TIME(8,0,0),
        EmployeeActivities[ActivityTimestampUTC]
    )
  1. For automatic daylight saving time handling, use the following DAX formula:
Local Time with DST = 
VAR CurrentTimezone = SELECTEDVALUE(EmployeeActivities[TimeZone], "UTC")
RETURN
    SWITCH(
        CurrentTimezone,
        "EST", DATEADD(EmployeeActivities[ActivityTimestampUTC], -5, HOUR),
        "PST", DATEADD(EmployeeActivities[ActivityTimestampUTC], -8, HOUR),
        EmployeeActivities[ActivityTimestampUTC]
    )
  1. Create a time-based visual (e.g., a line chart) using the “Local Time with DST” measure.
  2. Add a slicer for the TimeZone column to allow users to switch between different time zones.

Key Concepts

  • The SWITCH function allows for different time zone conversions.
  • SELECTEDVALUE handles default cases when no specific time zone is selected.
  • DATEADD automatically adjusts for daylight saving time, eliminating manual updates.

Troubleshooting

  • Common issues include incorrect time zone assignments and unexpected results during DST transitions.
  • Ensure your time zone column is correctly populated and consider using a date table for more complex scenarios.
  • If you encounter discrepancies, double-check your UTC data source for accuracy.
  • For multi-time zone reports, remember to clearly label which time zone is being displayed to avoid confusion.