How to Display in Power BI the Last Refresh Date and Time in EST/EDT

It is a best practice in Power BI report development to display the date and time of the last dataset refresh.  However, when you do a dataset refresh in the Power BI Service and to obtain the date and time of that refresh you use the M functions DateTime.LocalNow(), DateTimeZone.LocalNow(), or DateTimeZone.FixedLocalNow(), for example, you will get a result in Coordinated Universal Time (UTC).  Even if you label the date and time with “UTC” you may get complaints that the information displayed is wrong or suspicious.

Therefore, you should convert this UTC data to a time zone that makes sense to your users.  And in the case that you have users in different countries or time zones, you should include the time zone of the date and time being displayed.

Below we provide an M query called Dataset Refresh Info that converts the current date and time from UTC to EDT or to EST, whichever is applicable.  For reference, certain parts of North America and the Caribbean observe Eastern Daylight Time (EDT) in the summer and Eastern Standard Time in the winter (see here).  EDT is four hours behind UTC (commonly referred to as UTC -4) and EST is five hours behind UTC (UTC -5).

The script uses a reference table to determine whether the current date falls between the EDT date ranges and then apply the corresponding offset from UTC (see source).

This is the M code for the Daylight Savings table, which covers the years from 2018 to 2030:

let
  Source = Table.FromRecords (
        {
        [Year = 2018,  Start = "3/11/2018", End = "11/4/2018"]
        , [Year = 2019,  Start = "3/10/2019", End = "11/3/2019"]
        , [Year = 2020,  Start = "3/8/2020", End = "11/1/2020"]
        , [Year = 2021,  Start = "3/14/2021", End = "11/7/2021"]
        , [Year = 2022,  Start = "3/13/2022", End = "11/6/2022"]
        , [Year = 2023,  Start = "3/12/2023", End = "11/5/2023"]
        , [Year = 2024,  Start = "3/10/2024", End = "11/3/2024"]
        , [Year = 2025,  Start = "3/9/2025", End = "11/2/2025"]
        , [Year = 2026,  Start = "3/8/2026", End = "11/1/2026"]
        , [Year = 2027,  Start = "3/14/2027", End = "11/7/2027"]
        , [Year = 2028,  Start = "3/12/2028", End = "11/5/2028"]
        , [Year = 2029,  Start = "3/11/2029", End = "11/4/2029"]
        , [Year = 2030,  Start = "3/10/2030", End = "11/3/2030"]
        }
    ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Start", type date}, {"End", type date}, {"Year", type text}})
in
  #"Changed Type"

This is the code for the Dataset Refresh Info table:

let
    Source = DateTimeZone.UtcNow(),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed as UTC" = Table.RenameColumns(#"Converted to Table",{{"Column1", "UTC Now Date/Time"}}),
    #"Changed Type to DTZ" = Table.TransformColumnTypes(#"Renamed as UTC",{{"UTC Now Date/Time", type datetimezone}}),
    #"Copy the Column" = Table.AddColumn(#"Changed Type to DTZ", "UTC Now Date", each [#"UTC Now Date/Time"]),
    #"Changed Type to Date" = Table.TransformColumnTypes(#"Copy the Column",{{"UTC Now Date", type date}}),
    #"Extracted Year" = Table.AddColumn(#"Changed Type to Date", "Year", each Date.Year([#"UTC Now Date"])),
    #"Made Year a Whole Number" = Table.TransformColumnTypes(#"Extracted Year",{{"Year", type text}}),
    #"Merged with Daylight Savings" = Table.NestedJoin(#"Made Year a Whole Number",{"Year"},#"Daylight Savings",{"Year"},"Daylight Savings",JoinKind.Inner),
    #"Expanded Daylight Savings" = Table.ExpandTableColumn(#"Merged with Daylight Savings", "Daylight Savings", {"Start", "End"}, {"Daylight Savings.Start", "Daylight Savings.End"}),
    #"Calculated Offset to EST" = Table.AddColumn(#"Expanded Daylight Savings", "EST Offset Hours", each if [UTC Now Date] >= [Daylight Savings.Start] and [UTC Now Date] <= [Daylight Savings.End] then -4 else -5),
    #"Made Offset a Whole Number" = Table.TransformColumnTypes(#"Calculated Offset to EST",{{"EST Offset Hours", Int64.Type}}),
    #"Added Time Zone Column" = Table.AddColumn(#"Made Offset a Whole Number", "Time Zone", each if [UTC Now Date] >= [Daylight Savings.Start] and [UTC Now Date] <= [Daylight Savings.End] then "EDT" else "EST"),
    #"Added Last Refresh Column" = Table.AddColumn(#"Added Time Zone Column", "Last Refresh Date/Time", each DateTimeZone.SwitchZone ( [#"UTC Now Date/Time"], [EST Offset Hours] )),
    #"Changed Types" = Table.TransformColumnTypes(#"Added Last Refresh Column",{{"Last Refresh Date/Time", type datetimezone}, {"Time Zone", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Types",{"UTC Now Date/Time", "Time Zone", "Last Refresh Date/Time"})
in
    #"Removed Other Columns"

This is an example result:

The first column gives the current UTC date and time and the last column shows the converted date and time; in this case, the date falls in the EDT date range for 2019.

For use in a report as shown in the first image above, format the last column like this:

and then create a calculated column as follows:

Last Refresh Text =
    "Last data refresh: "
    & 'Dataset Info'[Last Refresh Date/Time]
    & " " & 'Dataset Info'[Time Zone]

For real-world reference, the Dataset Refresh Info query is part of a corporate template with the daylight savings table being made available via a dataflow entity.

More Information

See Solving DAX Time Zone Issue in Power BI by Reza Rad for additional background and other options for dealing with this issue.

Please follow and like us:

0 comments:

Leave a Reply

Your email address will not be published. Required fields are marked *