Time Zones and Relative Date Filters in Power BI Service

I recently ran across a tricky issue with relative date filters in PowerBI, which there wasn’t a decent amount of documentation on yet. So, I thought I’d share some findings and some formulas to help others deal with it accordingly if they find themselves in a similar situation.

Relative Date Filters in Power BI are a tremendously helpful feature, letting you create dynamic report filters in seconds, but before using one in your project, you should be mindful of when the report is going to be viewed, how often it will be refreshed, and what timezone the datetime value was measured in.

The reason timezone becomes important is because after the report is published to the Power BI cloud service, the effective timezone for relative filters and direct query caching becomes UTC time (also know as Greenwich Time).  If the date/time data used in your relative filter and your report users all reside in the UTC zone, then you have nothing to worry about.  For situations in the other 23 global time zones, you need to make sure you consider timezone encoding in your logical design.

Realizing this will often be an accident, because PowerBI desktop will use your local machine time to assess what time it is until you publish to the cloud.  Once you are relying on a globally-synchronized cloud-based report server, It will begin interpreting the effective timezone for your relative date filter to that of Great Britain, regardless of where your users are or what Microsoft Data Center installed your PowerBI instance from.

 

So, here are some useful things to know when designing your report and a starter pattern for building an effective relative date filter from another time zone:

 

A traditional Date/Time format looks like this:

YYYY-MM-DDThh:mm:ss

where T indicates the start of the time element.

Adding timezone encoding to provide context to where the time is ‘from’ is as simple as adding the UTC offset to the end of the date/time.  Here in Kansas, we either 5 or 6 hours behind UTC depending on daylight savings time. To create a ‘timezone aware’ date/time you simply need to add -05.00 or -06.00 to the end of the string and let your application know it is dealing with a date/time/zone data type.

 

Getting this context sorted out and implemented in a live report is a little more tricky, however.  I found the most effective way to do both timezone encoding and incorporate handling for DST was by using M functions in calculated columns based off the date being used for the relative date filter, called [DateTimeField] below.

First, you need to ensure your [DateTimeField] is actually of the datatype Date/Time/Zone, so convert this using the PowerQuery button, which generates the applied step with this M behind it:

= Table.TransformColumnTypes(#”Renamed Columns”,{{“DateTimeField”, type datetimezone}})

 

Next, generate your local offset from UTC time in order to modify your [DateTimeField] dynamically:

Local Offset = DateTimeZone.ZoneHours(DateTimeZone.LocalNow())

 

Then, leverage this with the Switch To Zone function to calculate the time equivalent of your record in UTC.

Switch To Zone = DateTimeZone.SwitchZone([DateTimeField],[Local Offset]) 

Note:  If you are working in a different timezone than the DateTimeZone data was sampled in, you will want to add that additional offset here.

 

Finally, truncate the offset from the UTC time for use in a relative date filter.  Relative Date filters do not accept DateTimeZone types, also you do not want this date to have any ‘zone context’ which might get used to change the time later…

Remove Zone = DateTimeZone.RemoveZone([Switch To Zone])

 

 

Here are a couple of records from Power Query which shows the transformation columns with actual data:

 

 

 

You’ll want to use the Relative Date (Remove Zone field above) for the Relative Date Filters applied to the page/ report level, and your original DateTimeField in any visuals to avoid any confusion about the context of the data by users.

 

Hope this helps!

 

Matt