Forum Discussion
Switch Time Zone on DateTime Columns based on User Selection
Keep the data stored in UTC.
Create a disconnected Time Zone table (EST, CST, PST, etc.) for the user to select from.
Apply the selected offset (ideally from a lookup table that includes DST rules) in measures for display.
Keep the date slicer based on the original UTC date or use a dedicated local calendar generated for the selected time zone.
The tricky part is exactly what you mentioned: when a timestamp crosses midnight after conversion, the local date changes. If the slicer remains on the UTC date, users can miss records around the day boundary. To avoid this, the slicer also needs to be based on the converted local date rather than the original UTC date.
One additional consideration is how many time zones you need to support. If it's only a handful (e.g., EST, CST, PST, UTC), precomputing the local date/time columns can be a practical solution. If you need to support many time zones or frequent changes, I'd push the conversion upstream (SQL/Fabric/Dataflow) using a proper time zone reference table that includes DST rules rather than trying to manage it in DAX.
Also, if the slicer truly needs to filter by the selected local date, I don't think there's a purely DAX-based solution with the native slicer because slicers operate on columns, not measures. At that point, the model needs to be designed around the local date requirement rather than just converting the displayed timestamps.
Super UserYou can add additional columns in Power Query that convert your UTC datetime column to the different time zones you want to support, then use a field parameter to let users switch between them based on a slicer selection.
However, keep in mind that datetime columns are highly cardinal and can significantly increase the size of your semantic model. Also, Power BI does not automatically detect and apply daylight saving time adjustments. If you need to support DST, you generally have to build additional logic around timezone rules, including the dates when the offset changes. This can become surprisingly complex because DST rules vary by country and can change over time (such as when the start and end within a given year).
Example timezone conversion
if Value.Is([UTC datetime], type datetimezone) then
DateTimeZone.RemoveZone(
DateTimeZone.SwitchZone([UTC datetime], 8)
)
else
DateTimeZone.RemoveZone(
DateTimeZone.SwitchZone(
DateTime.AddZone([UTC datetime], 0),
8
)
)