DST Refresh Date Function Power BI Service

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://p3adaptive.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:

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://p3adaptive.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.

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.

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.

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.