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 UserHello SahilKothekar,
There are really two problems hiding in this one: getting the UTC timestamps to show correctly in whatever time zone the user picks (DST included), and making sure the date slicer doesn't drift out of sync once you do that conversion.
For the display part, a disconnected Time Zone table works well. Let the user pick a time zone from the table, then use a measure to adjust the datetime based on their selection. I wouldn't hardcode the offsets though, since DST means the correct offset changes depending on the date. A lookup table is a better option.
The slicer is where people usually get tripped up. Measures can't be used as slicer fields, and calculated columns (including Power Query output) are only evaluated during data refresh, so they won't react to a user's time zone selection at runtime. The slicer can only filter on the stored column values in the model. It can't switch dynamically based on a measure.
Microsoft has a good explanation of the differences between measures, calculated columns and power query:
Use calculation options in Power BI Desktop.
If the business requirement is that the slicer filters by the user's local date, then you'll probably have to rework the model. If the business really needs the slicer to work off the user's local date, then the model will probably need to change.
One option is to precompute local date columns for each supported time zone. Another is to maintain a lookup table that contains the local dates and the DST rules for those time zones.
A couple of questions first:
1. Is the model Import or DirectQuery?
2. Where is the data coming from? SQL Server, Fabric, Azure SQL, Dataverse, or something else?
The answer will depend quite a bit on those details.