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

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

My hack for this issue.

 

I am using SQL Database as my source. so I added another table which gets "select getdate() as LastRefreshed" . this is updated everytime the report is refreshed and will have the acurate value in GMT of what you see on last refreshed value on your dataset. making changes accordingly to the timezone solved my problem. 

  • freder1ck's avatar
    freder1ck
    Kudo Kingpin
    7 years ago

    Anonymous

     

    I could not get your solution to work as is. However, this is what I finally got to work:

     

     

    let
    StandardLocal = -6, 
    UTC_Time = DateTimeZone.FixedUtcNow(),
    CurrentYear = Date.Year(DateTimeZone.SwitchZone(UTC_Time,StandardLocal)),
    DST_Start = #datetimezone(CurrentYear,3,13,2,0,0,-6,0),
    DST_End = #datetimezone(CurrentYear, 11, 6, 2, 0, 0, -5, 0), 
    AdjustDST = if UTC_Time >= DST_Start and UTC_Time < DST_End then StandardLocal + 1 else StandardLocal, 
    Source = #table(type table[LastRefresh=datetime], {{UTC_Time}}),
    #"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(),AdjustDST), DateTimeZone.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last Refresh Local", type datetimezone}})
in
    #"Changed Type1"

    It uses some variables at the top to define standard local time and the current start and end of daylight savings time.

    I also start with UTC so that I get the same results on my machine as in the service.