Power BI: display last update information

In some reports, it is very useful to display the last time I refresh my data so the audience can know the last update. Those are the steps to create it:

  • 1. Home -> transform data -> transform data
power bi
  • 2. Home -> New source -> blank query
  • 3. Home -> advanced editor
power bi

A popup window will appear, just delete everything and put this code:


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78S
    xonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0
    A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"dtDSTStart", type date}, {"dtDSTEnd", type date}}, "en-US"),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+0)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(type table[#"codeupdate"=datetimezone],{{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-1 + varDSTOffset,0)}})
in
    #"Last Refresh Date"
              

If all go well, I should have this:

power bi

Now I will change the name of the query by clicking on “home -> properties”, I will put for instance “lastupdate”. Once done, let’s exit the editor by clicking on “close & apply”.

power bi

Before to be able to display it, I will need to create a measure by clicking on “home -> new measure”:

power bi

And I will put this formula:

"Last Update GMT "&FORMAT(MAX('table'[argument]),"DD/MM/YYYY HH:MM")

power bi

Now let’s take a look to the result, I will create a card and put the measure I just created:

power bi

And here the result:

power bi

The time is displaying in GMT, but for instance, if I want to display it in CET time zone, I will have to go back to the advanced editor and change the “-1” to “+1” and of course, in the formula, change “GMT” by “CET” or remove it and just let “last update”.

power bi power bi

Interesting Topics