Timezone conversions

[Manaswini94]

Hi All,

I have a requirement where users would like to see the timestamps in destination state local time. Currently i have data in ZULU(UTC) time and a destination state column which has data like NY, AR,TN etc... Can someone help me on how to convert the timestamps based on destination state in power bi?

[Manaswini94]

Hi, I couldnt find the solution for this. However I have created a table with states, timezones and offset's then joined with another table with destination state. However the formulas are not working to convert the timstamps. could someone help me with this?

[technolog]

To convert the timestamps based on the destination state in Power BI, you can make use of DAX formulas combined with a lookup table for time zones. Here's a step-by-step guide:

1. Create a Time Zone Lookup Table: You need a table that maps each state abbreviation to its respective time zone offset. For instance:
   
   State | TimeZone
   ------|---------
   NY | -5
   AR | -6
   TN | -6
   ... | ...
   

   Note: Adjust the time offsets based on Daylight Saving Time if necessary or include an additional column to flag states that observe it.

2. Relationship: Ensure there's a relationship between your data table and the time zone lookup table based on the state abbreviation.

3. DAX formula: Once the relationship is established, you can use DAX to create a calculated column or measure that adjusts the UTC time for each row based on the state's time zone. Here's a simple example:

   LocalTime = 'DataTable'[ZULU Time] + LOOKUPVALUE('TimeZoneLookupTable'[TimeZone], 'TimeZoneLookupTable'[State], 'DataTable'[State])
   

   Here, 'DataTable'[ZULU Time] is your timestamp in ZULU (UTC) time, and the LOOKUPVALUE function fetches the time zone offset for each state.

4. Adjust for Daylight Saving Time (optional): If you have a flag in your time zone lookup table for states that observe daylight saving time, you can further refine the DAX formula to adjust for it.

   The above method is a basic approach. Actual implementation can vary based on the exact requirements and data structures. If states span multiple time zones or if you have specific date ranges to adjust for daylight saving time, things can get more complex.

   If your dataset is large, consider performance implications of row-by-row operations and try to optimize your DAX expressions accordingly.

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[v-zhangti]

Hi, @Manaswini94 

Please refer to the following links in the hopes of helping you.

Conversion UTC to local time date - Microsoft Power BI Community

How to convert UTC into Local Time Zone in Power BI Report using DAX? - PowerBI Talks

Converting UTC to local datetime in Power BI – Cloud BI

Convert UTC to Local Time in Power BI - IntelliTect

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.