Power BI Blog: Refreshing Time on Power BI Service – Part 2
23 May 2024
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:
CurrentUTC = DateTime.From(DateTimeZone.UtcNow())
and we also extract the year value from CurrentUTC:
CurrentYear = Date.Year(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:
Date.Day(Date.EndOfWeek(#date(CurrentYear, 4, 1))) - 1
Just to caution for the case that the first Sunday is the first day of the month, we included the following if-statements:
AprilPoint = if
Date.Day(Date.EndOfWeek(#date(CurrentYear, 4, 1))) = 1
then #datetime(CurrentYear, 3, 31, 16, 0, 0)
else #datetime(CurrentYear, 4,
Date.Day(Date.EndOfWeek(#date(CurrentYear, 4, 1))) - 1, 16, 0, 0)
We can thus obtain the 4pm of the correct date, and we do the same for October:
OctoberPoint = if
Date.Day(Date.EndOfWeek(#date(CurrentYear, 10, 1))) = 1
then #datetime(CurrentYear, 9, 30, 16, 0, 0)
else #datetime(CurrentYear, 10,
Date.Day(Date.EndOfWeek(#date(CurrentYear, 10, 1))) - 1, 16, 0, 0)
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:
TimeZoneAdjustment = if CurrentUTC < OctoberPoint then 10 else 11
Finally, we can use the DateTimeZone.SwitchZone() function, with our variable TimeZoneAdjustment, to switch from UTC to Sydney / Melbourne:
= DateTimeZone.RemoveZone(
DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), TimeZoneAdjustment))
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:
https://worldtimeapi.org/api/timezone/[time zone]
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.
