Tag: GMT

Have you ever had this experience? You build up your Power BI report using Power BI Desktop, and then when you publish and view the same report in the service, the date/time values are off by a set number of hours. I certainly run into this often enough that it warrants a blog post.

That you’ll notice is that this offset corresponds with the difference between your time zone and UTC (Coordinated Universal Time), the universal time standard. Now, you’ll notice that I didn’t say GMT (Greenwich Mean Time) which many people take to be the time standard, but the difference is that UTC is a time standard, and GMT is a time zone. GMT observes daylight savings time, and UTC does not – it remains constant. In my case, my time zone is Eastern, which is 4 hours behind UTC in the summer, and 5 hours behind in the winter. It is always 5 hours behind GMT. This distinction becomes important as you’ll see.

Coming back to our problem, we will see this problem if we have used the Date/Time/Timezone property in a Power BI Query (or a Power Query in Excel). When one of these column types are converted to Date/Time, the value is automatically localized. The reason that we see the different values is that when it is localized in Power BI Desktop, the machine’s time zone is used to perform the conversion. When this conversion happens in the Power BI service, UTC is used, and this results in different times being used in the browser. This difference can easily throw of any measures that depend are based on time values.

Further complicating things is the fact that DAX (Power Pivot) has no concept of Date/Time/Timezone and all columns of that type are brought into the model without the automatic conversion to local. So, how do we deal with this discrepancy?

We can’t rely on the Power BI service to automatically show the viewer the correct time for the location they happen to be in, so we need to be specific about the time zone that we’re working with. In my case, I need to do this for my tyGraph for Twitter reports, like this one for Microsoft Ignite 2017, or most recently in this session browser for Ignite. For these types of reports, I adjust the times to match the time zone of the events themselves. To do this, I use the modelling capabilities in Power BI desktop to create a new calculated column in the same table that contains the UTC based date.

In DAX, time calculations are done in decimal fractions of a day. Therefore, to create a new column named StartLocal by subtracting 4 hours from a date/time column named StartUTC in a table named Sessions, the formula is:

StartLocal = Sessions[StartUTC]) - 4/24

If the source column contains null or blank values, they will be returned as 1899-12-30 4:00 AM using the formula above, so it’s a good idea to test for this case and return nulls when appropriate. The above formula then becomes:

StartLocal = If(IsBlank(Sessions[StartUTC]),BLANK(),Sessions[StartUTC] - 4/24)

This calculation will reliably return the time in Eastern Daylight Time (UTC -4) for a column that has UTC date/time values in both the Desktop and in the service. If all subsequent time calculations are based on this column, then it is a simple matter of substituting in the appropriate UTC time offset to the calculation above to show the time in a desired time zone.

It would be nice if the report would allow us to specify a “home” time zone with which to base all conversions on. Time zone could then be a property of the report. Until such a time as that happens, this should prove a suitable, if complicated workaround.

4 Comments