Forum Discussion
How to normalize time zones
I have a data table with times from multiple different time zones. Looking for a way to normalize the times so that they all appear as Central Time within the report. So far I have not found a dynamic way to do that works.
Hi schrjenn
First, creating a static Time Zone Offset Table that contains all time zones and their offsets relative to UTC.Assume your data table contains the following columns:
Then use DAX to create a calculat column converted to UTC.
UTC_Time = VAR TimeZoneOffset = LOOKUPVALUE( 'Time zone offset'[UTCOffset], 'Time zone offset'[TimeZone], 'DataTable'[TimeZone] ) RETURN 'DataTable'[Timestamp] + TimeZoneOffset / 24Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hello schrjenn,
Can you please try this approach:
Adjusted Time = DATEADD( 'Table'[OriginalTime], LOOKUPVALUE('TimeZoneOffsets'[Offset], 'TimeZoneOffsets'[TimeZone], 'Table'[TimeZone]), HOUR )Are the original data points in UTC or do they originally come from different timezones?
The originial data points are in various time zones ranging from all of the U.S. time zones, India, and Philippines. Hoping to convert all of them to UTC.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi schrjenn
First, creating a static Time Zone Offset Table that contains all time zones and their offsets relative to UTC.Assume your data table contains the following columns:
Then use DAX to create a calculat column converted to UTC.
UTC_Time = VAR TimeZoneOffset = LOOKUPVALUE( 'Time zone offset'[UTCOffset], 'Time zone offset'[TimeZone], 'DataTable'[TimeZone] ) RETURN 'DataTable'[Timestamp] + TimeZoneOffset / 24Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
