Category: Uncategorized

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:

  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}})
  #"Changed Type"

This is the code for the Dataset Refresh Info table:

    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"})
    #"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:

Using Bookmarks in Power BI for an Enhanced Experience Selecting Common Date Periods

Many popular uses of bookmarks involve showing and hiding content based on user clicks on buttons.  A scenario I recently encountered and solved with bookmarks had to do with providing users the ability to quickly filter a page’s contents by a date period.  Users wanted to be able to switch between “this month”, “last month”, and a free-month selection.

For the first two options, a relative date filter can do the work but would require at least two clicks, and the free-month option would entail some type of dropdown list.  Moreover, two slicers would take a lot of space and both being about the same date might be confusing to the user.  This effort was part of an update to an existing report and there wasn’t much free space available.

The solution was to have three buttons to let the user switch between these filters.  The final version looked something like this, in its three possible states when the current month is May of 2019 (“Current” is the default view):




To implement this, we had a hidden relative date filter visual that was toggled via bookmarks from “Is in the last 1 month(s)” to “Is in this month” by the Prior and Current buttons, respectively.  This is turn updated the value shown by the card on the left.  For example:

Note the visible card and the hidden slicer.

The “Select…” button’s bookmark (Monthly Select) hides the card, clears the relative date filter, and shows a dropdown slicer.

See the effect in action:

This technique may be generalized for use in any kind of situation where filter presets may enhance the user’s experience.

Please follow and like us:

Using a Power BI Scatter Chart to Visualize Control of Congress

In this post I will show you how I built this pretty chart that depicts which party controled each house of the United States Congress (the Senate and the House of Representatives) between 1968 and 1980:

Final Chart

The chart displays, per year, the party that controls each house and the relative majority under which control is held.  The first item is given by the color of the dots and the second by their size.  Hovering over any of the dots shows this:


I got the raw data from Wikipedia, and it looks like this:


I had to format the data in a special way to build the chart, especially for the year-by-year continuity.  This is because elections are not held every year but are staggered.  In the Senate, seats are held for six years but elections occur every two years for approximately one-third of the seats.  In the House, elections are held every two years for all members.  So after some M programming and a bit of DAX I got it into this shape:


To build the scatter chart, we need two measures, one for the X axis and one for the Y axis.  For the X axis, I used the Begin Year and for the Y axis the House Type (1 for Senate, 2 for House).   Using Controlling Party for the legend provided the colors and the size was given by the Majority Percentage.  This is the final configuration:


The last touch was to use an overlaid text box to label the final Y axis:

Y Axis

As a final note, the Quick Insights feature of Power BI gave me the idea for this chart:


View the complete report here.

Please follow and like us: