Forum Discussion
Show Date/time based on user's time zone
Hi Anonymous
While M has built-in functions to handle timezones, none of them automatically shifts to the user's. The service uses Utc while Desktop is the user's. M will not know what timezone those datetimes are in so any datetime column converted to datetimezone will be +0 if refreshed in the service. You can convert a datetime column to datetimezone with an offset value (like 10.5 for GMT+10:30) using DateTimeZone.AddZone but knowing what timezone to add is key. Either way, you get to select and load only a specific timezone - Utc or user-defined.
Super UserHi Anonymous
Your other workaround is to crossjoin all timezones with the distinct values of your datetime columns, create a many to many relationship between the original datetime column and the crossjoined one and use the timezone from the crossjoined table in the slicer.
Crossjoined Table =
VAR Timezones =
SELECTCOLUMNS ( GENERATESERIES ( -23.5, 23.5, 0.5 ), "TimeZone", [Value] )
VAR DistinctDateTimes =
DISTINCT ( 'fact table'[datetime] )
RETURN
CROSSJOIN ( Timezones, DistinctDateTimes )
Be aware while this is possible, this might negatively impact the performance of your model.