Logo
banner

Blogs

Power BI > How to handle multiple time zones?

, September 18, 2020 6419 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!

mm

Inkey

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

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

  2. Saravanan says:

    Hello,
    This was a great article. I have couple of questions. It would be great if you could share your inputs to the below questions
    1. How’s the date filter from date table filtering the data from appointment table.
    2. How you’re handle appointments dates role over to next day based on the timezone
    Please share the .pbix file to the email address

    • Admin says:

      Hello,
      Thank you.
      Below are the responses:
      1. [Timezone Appointment Date] takes care of it.
      2. The implementation itself takes care of it.

  3. Laura says:

    Does this work with Direct query model?

  4. Binay Kumar says:

    Hi , i have few doubts
    1)how date[dates] is filtering the table Timezone appointment date .
    2)if we are creating a relation between date table and appoinment table ,the bar chart stops working .
    ## can you please share the pbix file on this email .

    • Admin says:

      Hi Binay,

      1. The measure ‘Total Appointments’ handles this.
      2. We do no need to create relationship between Date Table and Appointment Table.

Leave a Reply

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

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?