Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Have you ever wanted to know exactly when your data was last updated in Power BI? It’s important to know when your data is accurate and up-to-date. But, did you know the built-in Power Query method for finding the last refresh time is not always reliable?

Especially when your refresh takes place in a different time zone. In this article, we’ll show you a simple and reliable way to create a last refresh datetime value in Power BI, that takes into account the correct daylight savings time. So you can be sure your data is accurate and up-to-date all the time. Let’s get started!

Table of contents
Create Last Refresh Time that respects Daylight Saving in Power Query

Retrieve Local DateTime

The easiest way to create a last refresh datetime value in Power BI 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 a scheduled refresh, it may be that the location where your 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 where your data is stored in, followed by the location.

Important

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 last refresh Date Time stamp in your Power BI report, therefore, shows the time in Ireland. In the Netherlands, where I live, daylight saving time is always 2 hours ahead of Ireland. Therefore, reports show the wrong time.

So, how can we handle this so the function always returns at the right time? And how do we make sure the time accounts for the Daylight Savings time, too (GMT + 1 in winter and GMT + 2 in summer)?

Fix: DateTimeZone with Daylight Saving

The key to making 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 the following:

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

The trick is to convert UTC to the local time. To do that, 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 on the last Sunday of March
  • Wintertime starts on 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 last updated timestamp 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

And that’s the last piece of the puzzle to turn UTC to local for your last refresh date in Power BI.

Code Fix: Last Refresh Date

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. And that should give us some peace of mind.

You can also allow your users 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 time zones.

(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

Conclusion

So there you have it, a simple and reliable way to create a last refresh datetime value that takes into account daylight savings time. Now, you can be sure that your data is accurate and up-to-date, even during the times of the year when the clocks change.

Remember, the built-in Power Query method using the DateTime.LocalNow function can be unreliable, so this method is the preferred one. Give it a try and see how it works for you! Keep in mind that this approach can be applied to other similar scenarios to make sure your data is accurate and reliable.

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

Share on:

Latest from my blog

  1. Here is a function I made (basing to your code) to change UTC datetime column to your local fixed timezone.

    (UTCDateTime as datetime) =>
    let
    Summer_GMT_Offset = #duration(0, 3, 0, 0), // EET, Finland only
    Winter_GMT_Offset = #duration(0, 2, 0, 0), // EET, Finland only
    UTCDate = DateTime.Date(UTCDateTime),
    StartSummerDate = Date.StartOfWeek( #date( Date.Year( UTCDate ) , 3 , 31 ), Day.Sunday ),
    StartWinterDate = Date.StartOfWeek( #date( Date.Year( UTCDate ) , 10, 31 ), Day.Sunday ),
    UTC_Offset = if UTCDate >= StartSummerDate and UTCDate < StartWinterDate then Summer_GMT_Offset else Winter_GMT_Offset,
    Fixed_DateTime = UTCDateTime + UTC_Offset
    in
    Fixed_DateTime

    Reply
  2. I’ve used that lookup trick via using only measures to display data that has to be shown in local time, each measure going through a calculation group that modifies the underlying measures’ results with the users’ looked-up offset. What’s tricky is when you use this with workday measures (e.g. when does your week and day start and end?); it’s worse for modeling processes that hand off stuff between timezones.

    Reply
  3. Thanks Rick I found your post very useful.

    I have questions though. Your method is supposed to show the refresh date anytime I click refresh button in PowerBI or anytime I schedule the report to refresh right?

    Is there a way to show only the date for when the underlying data (table) used to create a report is updated? Because I do not want the card changing dates anytime refresh button is clicked.

    A little scenario will help you better understand what I mean. I just updated the data (table) for my PowerBI reports. I used your code in a card visual and it rightly shows the time I updated the data. The problem is anytime I click refresh, it gives me a different time even though the underlying data remains the same. I only want to the card to display visual for when the underlying data was updated and not when the refresh button clicked.

    I hope this makes my query clearer and hope you have an answer because I have googled this for sometime and every article I have seen only talks about refresh

    Reply
  4. I want to get time zone of user who has signed in power bi service and want to show it in a visual, how can I achieve it.

    Reply
  5. Hi Rick,
    Thanks for this article and I love your ! As for examples from other time zones, here in Canada, Toronto we are not so lucky and time changes Second Sunday in March and First Sunday in November so it’s a bit more complicated :-). I’ve included my M code for anybody interested, it adds the columns to the table for easy checking:

    #"Added Custom_FirstSundayInNovember" = Table.AddColumn(#"Renamed Columns", "FirstSundayInNovember-DaylightSavingEnd", each Date.AddDays(#date(Date.Year(UTC_Date), 11, 1), if Date.DayOfWeek(#date(Date.Year(UTC_Date), 11, 1), Day.Sunday) > 0 then 7 - Date.DayOfWeek(#date(Date.Year(UTC_Date), 11, 1), Day.Sunday) else 0)),
        #"Added Custom_SecondSundayInMarch" = Table.AddColumn(#"Added Custom_FirstSundayInNovember", "SecondSundayInMarch-DaylightSavingStarts", each Date.AddDays(#date(Date.Year(UTC_Date), 3, 1), if Date.DayOfWeek(#date(Date.Year(UTC_Date), 3, 1), Day.Sunday) > 0 then 14 - Date.DayOfWeek(#date(Date.Year(UTC_Date), 3, 1), Day.Sunday) else 0)),

    Regards,

    Reply
    • Hi Aurora,

      So glad to read you got the code working for the Toronto daylight savings. Many thanks for sharing. For easier copy-pasting that would make the following code:

      let
        UTC_DateTimeZone             = DateTimeZone.UtcNow(), 
        UTC_Date                     = Date.From ( UTC_DateTimeZone ), 
        FirstSundayInNovember = 
          Date.AddDays (
            #date ( Date.Year ( UTC_Date ), 11, 1 ), 
            if Date.DayOfWeek ( #date ( Date.Year ( UTC_Date ), 11, 1 ), Day.Sunday ) > 0 
               then 7 - Date.DayOfWeek ( #date ( Date.Year ( UTC_Date ), 11, 1 ), Day.Sunday )
               else 0
        ), 
        SecondSundayInMarch = 
          Date.AddDays (
            #date ( Date.Year ( UTC_Date ), 3, 1 ), 
            if Date.DayOfWeek ( #date ( Date.Year ( UTC_Date ), 3, 1 ), Day.Sunday ) > 0 
               then 14 - Date.DayOfWeek ( #date ( Date.Year ( UTC_Date ), 3, 1 ), Day.Sunday )
               else 0
        ), 
        UTC_Offset                   = 
          if UTC_Date >= SecondSundayInMarch and UTC_Date < FirstSundayInNovember 
             then - 4
             else - 5, 
        CET_Timezone = DateTimeZone.SwitchZone ( UTC_DateTimeZone, UTC_Offset )
      in
        CET_Timezone
      Reply
  6. 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
    • This should help you out. And feel free to change the offset numbers to fit your Timezone. It should work for any Daylight Saving Time 😁👌

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.