Fix Last Refresh Date/Time in Power BI (Incl Daylight Savings)

I love adding a last refresh date and time to reports. This gives the user an indication of how recent your dataset retrieved data and should give your user peace-of-mind to see whether the scheduled refresh actually works. But there’s a big problem with most approaches. They return different results on Power BI desktop than in the Power BI service, which can be very frustrating. In this blogpost I explain the issue and how to solve it.

Retrieve Local DateTime

An easy way to create a refresh time-stamp is by using Power Query. Most solutions I find use the DateTime.LocalNow function to get the current date and time:

= DateTime.LocalNow() // Returns the current DateTime value of your machine

The DateTime.LocalNow function returns the current date and time of the machine where the refresh takes place. In some situations this returns the expected results, but beware! You may not always get the expected value when you publish your report to the Power BI service. When you configure your report with scheduled refresh, it may be that the location where you report refreshes is in a different country than where your users live.

You can check this by going to the Power BI Service.

About Power BI Settings

Click the Question Mark in the top right corner -> click About Power BI.

Last Refresh Location Dataset

You now get a pop-up that shows details about your Power BI tenant. One of these lines shows Your data is stored in, followed by the location respective location. Here’s the thing. The location where the scheduled refresh takes place, impacts the datetime value returned by the DateTime.LocalNow function. In the above picture the data resides in Ireland.

The latest refresh DateTime value therefore shows the time in Ireland. In the Netherlands, where I live, the time is always 2 hours ahead. Reports therefore show the wrong time. So how can we handle this so the function always returns the right time? And how do we make sure the time accounts for the Daylight Saving time too (GMT + 1 in winter and GMT + 2 in summer)?

Fix: DateTimeZone with Daylight Saving

The key to make this work is to make use of the DateTimeZone.UtcNow() function. This function returns the UTC (Universal Time Coordinated) value, previously known as GMT. No matter the location of your machine, this function always returns GMT + 0 / UTC time.

For retrieving both the DateTimeZone value and the UTC Date you can then use:

=  UTC_DateTimeZone = DateTimeZone.UtcNow()
=  UTC_Date         = Date.From( UTC_DateTimeZone )

Next, you need to account for Daylight Saving Time (winter and summertime). For the logic we need to know the start of each period. So when do they both start?

  • Summertime starts at the last sunday of March
  • Wintertime starts at the last sunday of October

It’s a little tricky to get that date, but bear with me. To find the last Sunday in March you can do the following:

= #date( Date.Year( UTC_Date ), 3, 31 ) // Returns last date in March

= Date.StartOfWeek( LastSundaymarch, Day.Sunday )
// Returns latest Sunday in March

You can easily combine these to find the Start of both the winter- and summertime:

  StartSummerTime  = 
    Date.StartOfWeek( #date( Date.Year( UTC_Date ), 3, 31),  Day.Sunday ), 
  StartWinterTime  = 
    Date.StartOfWeek( #date( Date.Year( UTC_Date ), 10, 31 ), Day.Sunday ), 

You now need to define how much the time should be offset. The Netherlands uses UTC + 2 in Summertime and UTC +1 in wintertime. You can add a variable returning the offset value:

= if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime 
    then 2 else 1

With this in mind, you can now return the final time by offsetting the UTC time with the relevant number of hours. The function for this is DateTimeZone.SwitchZone.

= DateTimeZone.SwitchZone( UTC_DateTimeZone, UTC_Offset )
// Returns the current datetime value respecting Daytime Saving hours

Final Code

When you put all of this together, you get the following script:

let
  UTC_DateTimeZone = DateTimeZone.UtcNow(), 
  UTC_Date         = Date.From(UTC_DateTimeZone), 
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday), 
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday), 
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1, 
  CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
  CET_Timezone

This returns the correct DateTime value regardless of the machine that performs the scheduled refresh. You can also allow your user to provide the summertime and wintertime offsets by offering them a function. This is useful in case you need a template that needs to work for multiple timezones.

(Summer_GMT_Offset as number, Winter_GMT_Offset as number) =>
let
    UTC_DateTimeZone = DateTimeZone.UtcNow(),
    UTC_Date = Date.From( UTC_DateTimeZone ),
    StartSummerTime = Date.StartOfWeek( #date( Date.Year( UTC_Date ) , 3 , 31 ), Day.Sunday ),
    StartWinterTime = Date.StartOfWeek( #date( Date.Year( UTC_Date ) , 10, 31 ), Day.Sunday ),
    UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then Summer_GMT_Offset else Winter_GMT_Offset,
    CET_Timezone = DateTimeZone.SwitchZone( UTC_DateTimeZone, UTC_Offset)
in
    CET_Timezone

And with that, your report refresh time should always return the right value. Enjoy Power Query!

Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

5 thoughts on “Fix Last Refresh Date/Time in Power BI (Incl Daylight Savings)”

  1. When working for an executive with direct reports in the US, Ireland, and Australia, I found that DST comes and goes on different weeks in each of those countries so anybody looking to generate a DST solution should consult a reference such as timeanddate.com, where you can find not only the recent and next dates of DST but the rules. I’ve poked at a few solutions involving USERPRINCIPALNAME() and a user/location mapping table to show each user his/her local refresh time.

    Reply
    • Ken,

      That’s a great idea. It would make for a really interesting solution. You could create a UTC time, and have the offsets for summer and wintertime in a table. Then write your DAX with RLS that takes into account the accounts that logs in.

      You mention having poked at a few solutions. Did they work in the end?

      Reply

Leave a comment