Blogs

Power BI > How to handle multiple time zones?

Inkey, September 18, 2020 7427 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] )
RETURN
    ADDCOLUMNS (
        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] )
RETURN
    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 (
            Appointment,
            DATEVALUE ( [Timezone Appointment Date] ) IN VALUES ( Dates[Date] )
                && HOUR ( [Timezone Appointment Date] ) IN VALUES ( Time[Hour] )
        ),
        Appointment[Id]
    )
RETURN
    Appointments

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!