Forum Discussion

nirrobi's avatar
nirrobi
Helper V
10 years ago
Solved

last time refresh

Hi,   Is it possible to add to the report/dashboard the date and time of the last refresh?   Many thanks.   Nir
Need Help
  • idanco's avatar
    idanco
    10 years ago

    Hi nir,

     

    You can add a calculated column (Last Refresh Column) in the model with the formula =NOW()

    And measure Last Refresh Date :=MAX(Table[Last Refresh Column])

     

    Since calculated columns are calculated only on model refresh - it will make sure the calc is right

     

     

freder1ck's avatar
freder1ck
Kudo Kingpin
8 years ago

So, I realize now that the problem is not a time problem but really a custom calendar problem. Have your friendly neighborhood DBA put a DST column on the date table where if would be zero if Standard Time and +1 if daylight savings time. Or do this with a custom calendar in power query. Then add the DST offset to the current date and time. And because it's being handled on the date table, the date would actually different for the full day of starting and ending DST (don't refresh from 12am to 2am).


(DST) in most of the United States starts on the 2nd Sunday in March and ends on the 1st Sunday in November.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    No Need - the Powerquery Solution I Provided accurately reporst the local time irrespective of the location where the data was refreshed

    • vbaker's avatar
      vbaker
      Helper I
      8 years ago

      Anonymous

      Unfortunately, I don't believe that is accurate.

      In the Eastern Time Zone, we are currently -4, however during DST we are -5, so we would need to modify the "DateTimeZone.FixedUtcNow(),-8" during each season.

       

       

      let
          Source = #table(type table[LastRefresh=datetime], {{DateTimeZone.LocalNow()}}),
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
          #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),
          #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Last Refresh Local", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),-8)),
          #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last Refresh Local", type datetimezone}})
      in
          #"Changed Type1"

    • freder1ck's avatar
      freder1ck
      Kudo Kingpin
      8 years ago

      Anonymous:

      Your approach is brilliant. It uses the US locale to get the time zone shift.

      Anonymous wrote:

      Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),

      It worked fine in Power BI desktop, locally, but when I uploaded to the service it didn't work right.

       

      I think you're on the right track, but I am not quite there yet.