Power BI > How to handle multiple time zones?

, September 18, 2020 409 Views

We have often encountered requirements that need the date and time columns to change according to the timezones of the user. Conversion according to the timezone requires to add/subtract the offset hours to the DateTime field in UTC. As this is something that is not available in Power BI out-of-box, we have found an approach that addresses this issue.

I will explain this with a use case & the solution we have come up with.

Let’s assume that the DateTime fields in the dataset are already in UTC. If in your case, they are not, please create one.

Here, we have a dataset on Appointments and say, the user wants to know about the details on his/her appointments between two dates and between a certain time period.

For the sake of this blog, we have put the list of the different TimeZones into a slicer and assume that the user will select his/her TimeZone and will then filter the details.

For the Date & Time Slicer, we would need Date dimension and Time dimension.

Date Dimension can be created as below:

Dates = CALENDAR(DATE(2020,01,01),DATE(2020,12,31))

Time Dimension can be created as below:

Time =
VAR HourField =
    SELECTCOLUMNS ( GENERATESERIES ( ( 0 ), ( 23 ) ), "Hour", [Value] )
VAR MinuteField =
    SELECTCOLUMNS ( GENERATESERIES ( ( 0 ), ( 59 ) ), "Minute", [Value] )
VAR SecondsField =
    SELECTCOLUMNS ( GENERATESERIES ( ( 0 ), ( 59 ) ), "Second", [Value] )
        CROSSJOIN ( HourField, MinuteField, SecondsField ),
        "Time", TIME ( [Hour], [Minute], [Second] ),
        "TimeKey", FORMAT ( TIME ( [Hour], [Minute], [Second] ), "hhMMss" )

In addition to the above tables, we would also need some supporting tables to build our solution around. Mostly, these are self-explanatory.

Timezone Table: This table can either have all the possible Timezones or only the ones you need.

Appointment Table:

User Table:

There will be a relationship with [User].[Id] and [Appointments].[UserId] but we won’t be creating any relationship between the Date/Time dimension and the Appointments table because we need to change the Appointment Date as per the timezone selected. As calculated columns cannot be changed dynamically as per the slicer selection, we would need to handle this through a measure.

We would take the Offset Hour as per the selected Timezone and would add/subtract from the Appointment Date(UTC). It would be done as below:

Timezone Appointment Date =
VAR OffsetHours =
    SELECTEDVALUE ( Timezone[Offset Hour] ) / 24
VAR AppointmentDate =
    SELECTEDVALUE ( Appointment[Appointment Date] )
    IF ( ISBLANK ( AppointmentDate ), BLANK (), AppointmentDate + OffsetHours )

Here, we have divided the offset hours with 24 to convert hours into days. If we directly subtract the hours with the datetime field, it would add/subtract the days from it. So if we just add “5” from datetime, it would add 5 days. Hence, we need to divide 5 with 24 to convert it to days.

For the count of Appointments according to the selected date and time duration, the measure would be as below:

Total Appointments =
VAR Appointments =
    COUNTX (
        FILTER (
            DATEVALUE ( [Timezone Appointment Date] ) IN VALUES ( Dates[Date] )
                && HOUR ( [Timezone Appointment Date] ) IN VALUES ( Time[Hour] )

We have prepared a dashboard for Appointments in the selected Timezone Duration between the two dates and hours.

We can see here that after selecting different timezones, dates, and hours, we can get the number of appointments at specific hours and the details of the appointment in the table below.

If we do not want the user to select the timezone and the report should work according to the user’s timezone automatically then we would need a table with the User and their timezone. The timezone table could be related to the User table using TimezoneId. We can then apply RLS to the User table. This way the timezone table will be filtered as the User table is filtered.

We have devised the solution with the best approach we could think of. Please let us know in the comments section if you have a better solution. 

Happy DAX-ting!



INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

2 responses to “Power BI > How to handle multiple time zones?”

  1. Jayden says:

    Hi, thanks for the post. I have a question – what would you do to handle daylight savings, as areas that use daylight savings will not be a static offset throughout the entire year.

    • Admin says:

      For that, we can update the Offset Hours as per Daylight Savings time or you can add another column for the Offset Hours for Daylight Savings Time(Offset Hours-DST) and if the date filter period is in the duration of DST then we can use the other column(Offset Hours-DST) we created. Please let us know if this is unclear.

Leave a Reply toJayden Cancel Reply

Your email address will not be published. Required fields are marked *