Power BI Blog: Refreshing Time on Power BI Service – Part 2

Welcome to our Power BI blogs.  Over two weeks, we will show you how to manage
time-refreshing on Power BI Service.

When you manage dashboards on Power BI Service and try to refresh
anything involving Date/Time values, you will discover that Power BI Service
uses Coordinated Universal Time (UTC) instead of your local time.  You might work around this by manually adding
on the necessary time-zone adjustments (e.g. UTC+8 for Beijing) in your
visual measures or in the Power Query M code, but it can get messy where
daylight saving is relevant.

Last
week, we demonstrated the approach of obtaining web-API local time.  This week, we will show you an alternative
approach using Power Query M functions.

Obtain Date and Time using M

This
time, we shall perform some conditional coding in Power Query to decide whether
we need to be worrying about daylight saving and how to accordingly translate
UTC to the Sydney / Melbourne time.We
start with getting the current UTC date and time:

and we also extract the year value from CurrentUTC:    

For Sydney and Melbourne (or the Australian Eastern Standard
Time, AEST), daylight saving starts at 2am of the first Sunday of October and
ends next year at 3am of the first Sunday of April (usually!).  Under UTC, both times are 4pm of the previous
Saturday.

Thus,
to define our dividing time-points under UTC, we use the Date.EndOfWeek() function and subtract one [1] to get the Saturday before that Sunday:    

Just to caution for the case that the first
Sunday is the first day of the month, we included the following if-statements:    

We can thus obtain the 4pm of the correct date,
and we do the same for October:    

What we’ve obtained are dividing time-points for
daylight saving under UTC Date/Time.  We may
then use them to write the conditions. 
We will define a variable called TimeZoneAdjustment, which is the
adjustment hours from UTC to Sydney / Melbourne time:    

Finally, we can use the DateTimeZone.SwitchZone() function, with our variable TimeZoneAdjustment, to switch from UTC to Sydney / Melbourne:    

We’ve also used the DateTimeZone.RemoveZone() function to remove time zone information from the data.  We may then rename the column, change data
type to Date/Time and load it to the report for publishing.    

In summary, we have created two [2] Date/Time variables that
correctly reflect Sydney / Melbourne time even when published to Power BI
Service.  In the method above, we hardcoded
the date conditions which only work for the Sydney / Melbourne time.  This approach means we do not need to request
web data, which saves significant calculation time.  In contrast, last week’s
solution could
easily cope with selecting different time zones.

With
APIs from WorldTimeAPI.org, we have choices across numerous time zones:

The URL scheme is also easy to alter for different time
zones:

The only trade-off is slower refresh time from requesting
external data.

That
concludes our two-week series on publishing time on Power BI Service. Please stay tuned for more thoughts and
insights: http://www.sumproduct.com.