Adjusted Datetime Based Upon Selected Time Zone

Have you had an opportunity that you needed to develope a report which would be consumed globally? If there is datetime data, you need to be cautious how you would show it in your report.

Will it be based upon UTC? Local time? I came up with an idea that it allows an end user to choose their own timezone so that datetime in a report would be converted by the user’s own choice.

Preparing a table of timezone

First of all, let’s furnish a table with time zone data, name of time zone and utc offset value would be the minimum requirements. You may scrape or download data from the Internet.

You may download a csv file.

Allow report consumers to choose their own time offset

Let’s pretend you have a dataset containing datetime data. The datetime data should be set UTC time.

Once you loaded a table into Power BI, let’s create a measure. By the way, you do not need to make a relationship in the model as lookup function will be utilized here.

Do not forget to add a slicer with timezone name.

Now you are all set. Once you puck a timezone in the slicer, you would find the datetime value is adjusted accordingly.

1
2
3
4
5
6
7
8
9

Adjusted Datetime =:
    VAR CurrentSelectedTimeZone = SELECTEDVALUE(dim_timezone[tz_offset])
    VAR CalcItem = SELECTEDVALUE(dim_id[utc_end_datetime]) + DIVIDE( CurrentSelectedTimeZone, 24) 
    
    RETURN
    IF(
    NOT ISBLANK(SELECTEDVALUE(dim_id[utc_end_datetime])),
    CalcItem
    )

You may download a pbix file.