Forum Discussion
Timezone conversions
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?
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:
-
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.
-
Relationship: Ensure there's a relationship between your data table and the time zone lookup table based on the state abbreviation.
-
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.
-
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.
-
