Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Show Date/time based on user's time zone

Hi all, I have a date column and I want to show it dynamically based on the user's time zone.   I changed the format of the column to date/time/timezone and I thought Power Bi would handl...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    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.

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi danextian,
Sorry for asking lots of questions, but I'm a little confused.
I couldn't set a many to many relationship here and also I don't know how to connect the time zone column with our crossjoined table.
In the demo file you sent me, I added a new graph to show the switched time zones.
Could you please help me with how I should do this?

I couldn't upload a pbix file here so here's the link to transferxl.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    There's something I miss in the formula. I forgot to add the datetime based on the selected timezone. Should have been:

     

    Crossjoined Table =
VAR Timezones =
    SELECTCOLUMNS ( GENERATESERIES ( -23.5, 23.5, 0.5 ), "TimeZone", [Value] )
VAR DistinctDateTimes =
    DISTINCT ( 'fact table'[datetime] )
RETURN
    ADDCOLUMNS (
        CROSSJOIN ( Timezones, DistinctDateTimes ),
        "Switched DateTime", [DateTime] + DIVIDE ( [TimeZone], 24 )
    )

     

     

     

     

    Please see attached sample pbix