skip to Main Content

Daylight Savings Time Refresh Data in Power BI

Quick tip for DST Refresh Date function Power BI Service. I’ll put the code up front, and explain it below. I’ll also say a bit about how to use it at the end. The United States and other places, like Australia, have a pesky thing called Daylight Savings Time. This means that in Central Time US, the offset from Universal Time Coordinated (UTC) is sometimes -6 and other times it’s -5. While Power Query can convert time zones, it doesn’t handle DST. And, my users like to see when the reports were refreshed as a step in evaluating data quality. In 2019, US DST is from March 10 – November 3 (2 AM local time). So, the functions here need to be updated every year.

As promised, here’s the custom function. Fire up Power Query, add a new blank query, open the advanced editor and paste it in. Change “StandardLocal = -6” to your local time zone offset (Or, I have a version below where you can set StandardLocal as a parameter).

// fRefresh - Central Time
// Documentation: https://powerpivotpro.com/DST-Refresh-Date-Function-Power-BI-Service

let
    Source = () => 

let
   StandardLocal = -6, 
   UTC_Time = DateTimeZone.FixedUtcNow(),
   CurrentYear = Date.Year( DateTimeZone.SwitchZone( UTC_Time,StandardLocal)),
   DST_Start = Date.StartOfWeek(#datetimezone( CurrentYear,3,14,0,0,0,StandardLocal,0),Day.Sunday) + #duration(0,2,0,0),
   DST_End = Date.StartOfWeek( #datetimezone( CurrentYear, 11,7,0,0,0, StandardLocal+1,0),Day.Sunday) + #duration(0,2,0,0), 
   AdjustDST = if UTC_Time >= DST_Start and UTC_Time < DST_End then StandardLocal + 1 else StandardLocal, 
   Source = #table(type table [LastRefresh=datetimezone], {{UTC_Time}}),
   #"Add Local Column" = Table.AddColumn(Source, "Refreshed", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),AdjustDST), DateTime.Type),
   Refreshed = #"Add Local Column"{0}[Refreshed]
in
   Refreshed

in
    Source

Step-by-step: DST Refresh Date Function

You see that there are two lets and to ins in the code. There are also lines of comments at the top starting with //.

This is the outer let/in, which makes this query into a custom function:

let
Source = () =>
[…]
in
Source

Convert custom function to standard query

If you remove these parts, you can see the core of the function as a query instead, with all of the steps. Better yet, instead of deleting them, just comment them out with // at the beginning of the lines:

 

 

DST Refresh Date Function Power BI Service. Query setting steps.

Power Query steps as variables

Each line before Source is a variable of sorts. Each step is equal to a single value, and they don’t build on each other directly.

StandardLocal = -6, 
UTC_Time = DateTimeZone.FixedUtcNow(),
CurrentYear = 
     Date.Year(DateTimeZone.SwitchZone(UTC_Time,StandardLocal)),
DST_Start = Date.StartOfWeek( #datetimezone( CurrentYear,3,14,0,0,0, 
    StandardLocal,0),Day.Sunday) + #duration(0,2,0,0),
DST_End = Date.StartOfWeek( #datetimezone( CurrentYear,11,7,0,0,0, 
    StandardLocal+1,0),Day.Sunday) + #duration(0,2,0,0),  
AdjustDST = if UTC_Time >= DST_Start and UTC_Time < DST_End then StandardLocal + 1 else StandardLocal, 

These variable steps do all the heavy lifting. StandardLocal in Central Time US is -6. For Standard Eastern Time, use -5. Use -7 for Mountain, and -8 for Pacific. CurrentYear is the year in the current time zone. DST_Start and DST_End find the start and end of daylight savings time according to the current rules (Marcel Beug is brilliant!). Finally, AdjustDST is a conditional which swaps daylight time for standard if it’s within the daylight savings time range. Variables have two purposes here. First, they make the query easy to understand. Second, they make it easy to update. For example, whenever the rules for DST are changed.

Return the local date value

Once the calculations are done, we need to package it up as a final value. I’ve commented each of the three steps below:

Source = #table(type table [LastRefresh=datetimezone], {{UTC_Time}}),
// Using the built-in #table() function, I make a table with a column named LastRefresh with a type of datetimezone. Here, I put the UTC_Time. I use UTC to get control over the time conversion. 

#"Add Local Column" = Table.AddColumn(Source, "Refreshed", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),AdjustDST), DateTime.Type),
// Next, I add a column to the table, which has the local time using the AdjustDST step. In Central Time US, -6 or -5.

    Refreshed = #"Add Local Column"{0}[Refreshed]
// Finally, I drill down to the first value (index 0) in the [Refreshed] column of the previous step (#"Add Local Column"). This enables the value to load into a column as a single value, but if you prefer to invoke the function directly, you can remove this step and update the closing 'in' to refer to the previous step.

2nd Version: Custom function with Standard Local offset as a parameter

My original function has no parameters, because I always use the same time zone. However, here’s a version with local time as a parameter. To change it, I removed the step for StandardLocal, and inserted StandardLocal as a parameter inside the parenthesis for the function.

// fRefresh
// Standard local time as parameter version
// Documentation: https://powerpivotpro.com/DST-Refresh-Date-Function-Power-BI-Service

let
    Source = ( StandardLocal ) => 

let
        UTC_Time = DateTimeZone.FixedUtcNow(),
        CurrentYear = Date.Year(DateTimeZone.SwitchZone(UTC_Time,StandardLocal)),
        DST_Start = Date.StartOfWeek(#datetimezone(CurrentYear,3,14,0,0,0,StandardLocal,0),Day.Sunday) + #duration(0,2,0,0),
        DST_End = Date.StartOfWeek(#datetimezone(CurrentYear,11,7,0,0,0,StandardLocal+1,0),Day.Sunday) + #duration(0,2,0,0), 
        AdjustDST = if UTC_Time >= DST_Start and UTC_Time < DST_End then StandardLocal + 1 else StandardLocal,     Source = #table(type table [LastRefresh=datetimezone], {{UTC_Time}}),     #"Add Local Column" = Table.AddColumn(Source, "Refreshed", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),AdjustDST), DateTime.Type),
        Refreshed = #"Add Local Column"{0}[Refreshed]
    in
        Refreshed

in
    Source

So now that we have a function, what can we do with it? We can either invoke it as a new query, or we can add it as a new column.

Invoking a Custom Function as a new query

From the query list, click on the function version of the query. Then, put in the standard local time offset, and press the Invoke button. This will create a new query that will return a single value.

 

DST Refresh Date Function Power BI Service. Invoke custom function.

In order to load this to your model, you’ll need to change it to a table from the Transform tab of the ribbon. Be sure to set the type as date time and name the column something besides Column1.

 

DST Refresh Date Function Power BI Service. Transform to table.

Add column: Invoke Custom Function

Sometimes, I like my refresh time as a column in a data table. In your table, go to add column tab of the ribbon, and click Invoke Custom Column.

 

DST Refresh Date Function Power BI Service - add column: Invoke function in Custom Column

You’ll need to select the function, name the column, AND set the parameter. Now, add column: custom function figures you’re going to get your parameter from an existing column in your table, but in this case we will edit the button to the left of the parameter to change it from column to ‘any’, which lets us put in the value manually. As a result, I have added a California Refresh Time column to a table that already had a Central Time Refresh column.

Related Info

  • Marcel Beug: Re: Return the 2nd Tuesday of every Month
    From Community.PowerBI.com, this is an elegant way of getting the 2nd Tuesday of the month. I adapted it for the 2nd Sunday of March and the 1st Sunday of November. Without this critical piece, I would have to update the function manually every January!
  • Reza Rad: Solving DAX Time Zone Issue in Power BI
    For a while, I used Method 3 from this post– which gets the local time from a web site.
  • Rob Collie: Add a “Last Refreshed Date” Readout
    In this classic post, Rob shows how to get the refresh date from a transaction table, which may be a better solution for you. For me, I need the granularity of time refreshed because I’m updating the data throughout the day.
  • Chris Webb: Creating Tables in Power BI/Power Query M Code using #table()
    This is a great intro to creating tables in Power Query. I refer to it all the time.

Where It’s At:  The Intersection of Biz, Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.

Fred Kaffenberger

A teacher at heart, Fred loves that moment when someone struggling to solve a problem makes a breakthrough. Fred spent six years in client services and sales for an online work order system. After this, he used Excel to help streamline commercial real estate operations for a major telecommunications carrier, where he discovered Power Pivot and Power BI. He was thrilled at how these tools helped him work smarter and more systematically. As an English major, Fred knows that a knack for working with data can surface in surprising places.

This Post Has 9 Comments
  1. How does this account for when the UK moves to and from DST, which is 2 weeks after (3/24) in the spring and one week prior (10/27) in the fall?

    1. Great question, Brian!

      “BST begins at 01:00 GMT on the last Sunday of March and ends at 01:00 GMT (02:00 BST) on the last Sunday of October.” (Wikipedia). So, adapting Marcel Beug’s formula, we look for the week beginning on Sunday of the day March 31st and for November 30th. Change the Standard Time to equal zero (or some European variant) and rename everything from DST to BST for British Summer Time.

      BST_Start = Date.StartOfWeek(#datetimezone( CurrentYear,3,31,0,0,0,StandardLocal,0),Day.Sunday) + #duration(0,1,0,0),
      BST_End = Date.StartOfWeek( #datetimezone( CurrentYear, 10,30,0,0,0, StandardLocal+1,0),Day.Sunday) + #duration(0,1,0,0),
      AdjustBST = if UTC_Time >= BST_Start and UTC_Time < BST_End then StandardLocal + 1 else StandardLocal,

  2. I was frustrated by similar issues with preserving refresh time date. There are many ideas in this article that are helpful to my programs. Thank you.

    Here is the query editor part of the code I came up with. This is hardwired to Chicago Time which was OK for my purposes. Importantly, for my purposes, it solved the timezone name and UTC calculation issues:

    let
    Source = Csv.Document(Web.Contents(“http://worldtimeapi.org/api/timezone/America/Chicago.txt”),2,””,null,65001),
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type text}, {“Column2″, type text}}),
    #”Replaced Value” = Table.ReplaceValue(#”Changed Type”,”:”,””,Replacer.ReplaceText,{“Column1″}),
    #”Transposed Table” = Table.Transpose(#”Replaced Value”),
    #”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table”, [PromoteAllScalars=true]),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Promoted Headers”,{{“abbreviation”, type text}, {“datetime”, type datetimezone}, {“day_of_week”, Int64.Type}, {“day_of_year”, Int64.Type}, {“dst”, type logical}, {“dst_from”, type text}, {“dst_until”, type text}, {“timezone”, type text}, {“unixtime”, type number}, {“utc_offset”, type duration}, {“week_number”, Int64.Type}})
    in
    #”Changed Type1″

    Here was the simple DAX formula so the date and time of the refresh can be presented in a PowerBI page:

    atxt LstRfrshDate =
    VAR xLRDateTime =
    FORMAT(LASTDATE(fLstRfrshDate[datetime]),”mmm dd, yyyy hh:mm AM/PM”)

    VAR xLRDSTIndicator =
    CALCULATE( MAX( fLstRfrshDate[abbreviation]))

    VAR xLRUTCOffset =
    IF( MAX( fLstRfrshDate[utc_offset])<0,
    "-" & FORMAT( MAX( fLstRfrshDate[utc_offset]), "h:mm") ,
    "+" & FORMAT( MAX( fLstRfrshDate[utc_offset]), "h:mm"))

    RETURN
    xLRDateTime & " " & xLRDSTIndicator & " (" & xLRUTCOffset & " UTC)"

    There are several additional features I would like to add to the data query which are a bit beyond my current capabilities: Keep all the old update records; Add additional fields to each refresh update record at the time of the refresh including: record count for several of transaction data tables in the PowerBI file from which this Update Refresh was invoked, the file path for the PowerBI pbix file from which this date update was invoked, and the name of the pbix file from which the query update was invoked.

    Thank you for this article.

    Bill

  3. Great article Fred. Something to note, in my tenant, I had to add DateTimeZone.RemoveZone in the following step to get it to work in the PBI Service:

    #”Add Local Column” = Table.AddColumn(Source, “Refreshed”, each DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),AdjustDST)), DateTime.Type)

    I suspect that after invoking the function, converting to table, and converting to DateTime… in that last conversion, the PBI Service converts the value back to UTC, or the local time of the servers; but that’s just a guess. I’ve not spent enough time identifying root cause.

    I’m crossing my fingers this works long-term. Like you, I used Reza’s method of calling an API to get Local PDT for a while. Then I found this other post for converting UTC data to Local time: https://intellitect.com/convert-utc-local-time-daylight-savings-support-power-bi/. Thought the Power BI team did right by us customers and made it easier for us to display local time with DateTimeZone.ToLocal. Unfortunately, it doesn’t work for Last Refresh scenarios, which is when I found your post.

    Thanks again!

Leave a Comment or Question