Forum Discussion
last time refresh
Hi,
Is it possible to add to the report/dashboard the date and time of the last refresh?
Many thanks.
Nir
Hi,
Maybe this will solution will help,
http://www.powerpivotpro.com/2010/11/add-a-last-refreshed-date-readout/
I will try tommorow and update :-)
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
60 Replies
You can show this on a tile in a dashboard by clicking the ... on the top right hand corner of the tile, then click the edit pencil, then check the "Display last refresh time". The refresh time will now show at the top of the tile
This worked for me and takes into account that when the auto refesh on PowerBI.com occurs it is done in GMT time:
Enter this M formula into a blank query in the get data button:
= DateTimeZone.ToLocal(DateTime.AddZone(DateTime.LocalNow(),-10))
The -10 is becasue I am +10 GMT. Not sure why it needs to be negative but it is.
Hope this works.
Giles
GilesWalker wrote:This worked for me and takes into account that when the auto refesh on PowerBI.com occurs it is done in GMT time:
Enter this M formula into a blank query in the get data button:
= DateTimeZone.ToLocal(DateTime.AddZone(DateTime.LocalNow(),-10))
The -10 is becasue I am +10 GMT. Not sure why it needs to be negative but it is.
Hope this works.
Giles
Giles,Your formula was very helpful to me. However, instead of adjusting between two local timezones, I used UtcNow.
= DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-5)
And if there is no edit pencil? and no Tile Details? And no "last date refreshed"?
No way that I know of. This would be SUPER helpful. When you share out a dashboard, not everyone will be able to see when it was last refreshed without this. And this will be the first things people ask or want to know about..."How recent is this?"
Hi,
Maybe this will solution will help,
http://www.powerpivotpro.com/2010/11/add-a-last-refreshed-date-readout/
I will try tommorow and update :-)
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
Not sure when this feature was added but I just spotted it today
Click on the ... in your dashboard, clcik on the pencil to edit, select display last refresh
I am seeing all these people saying to hover over the tile and click the elipse button... but I do not get the edit or pencil icon when clicking the elipse button when in the dashboard or report. Is there an IE/Edge configuration I need to uncheck in order to get the edit (pencil) button when clicking the elipse button?
Thanks.
Hi vkdubw, I'm not aware of any issue. My dashboards are not affected by Chrome or Edge.
Are you able to post a screenshot?
I'm not sure if the source of the dashboard is an issue. Are you getitng your dashboard from a Content Pack or just creating it yourself?
Instead of just adding time zone Offsets (which Many suggest) ,
use the Following Power Query
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"This Give two Dates, base tiime zone of the refresh and the "Local Time" I am in Pacific and just Ran this in Desktop ift Gives teh Following
Because we have just "Sprung Forward" to Summer time
on Power BI Service it Gives the Following
How are you automatically accounting for Daylight Saving Time?
We have removed the time indication and are just showing the date as the lazy solution, or where it doesn't really matter if the time is out by an hour not bothering to fix it.
To deal with this across all the dashboards in the future we will
1) Only publish the date where time doesn't matter.
2) Adopt the solution suggested by Anonymous and using a SQL database that we have access to.
3) Another alternative is to scrape the time from a webpage that specifies the time in Summer time. Although I don't like the idea of introducing a dependency on an external website in out dashboards.
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.
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.
Hi all,
Best way to actually do this is to pull in this website using the web conector https://www.timeanddate.com/worldclock/uk/london
***edit*** go here https://www.timeanddate.com/worldclock/uk
Use a card to display the cities time you would like :)
far easier than all this coding!
Thank you trysodin! Your idea worked perfectly for me. My previous solution was working but after the recent switch from DST to PST, it was off by an hour. This is a much simpler solution. Thank you!
let
Source = Web.BrowserContents("https://www.timeanddate.com/worldclock/usa/san-francisco"),
#"Extracted Table From Html" = Html.Table(Source, {{"LastRefresh", "[id*=""qlook""]"}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","ST","ST; ",Replacer.ReplaceText,{"LastRefresh"})
in
#"Replaced Value"
nirrobi Is this for troubleshooting purposes? Just to point out, you can see when the dataset that supports the reports/dashboards was last refreshed. Just click the elipses of the dataset that currently has a scheduled refresh set and it will show you when the last refresh was, and when the next one is scheduled.
You could then assume that all reports / dashboard tiles should be updated based on that date/time.
Thanks for the reply.
Its not for troubleshooting but to know we the last refresh was.
Meantime I have problem with the schedule refresh and want to know the last time the dataset was refresh, is it possible?
I want to have tile with last time the dataset was refresh.
Regards,
Nir,
This was showing the last time I ran my report but now seems to have disappeared again ... anyone know why please?
Hi All,
Add a column to your dataset with the below formula.
Last Refresh Date Time =DateTimeZone.LocalNow()
This will get refresh only,when you click the refresh button. It is working 100% correct. I'm using in my current project PBI Reports.
Add a column to your dataset with the below formula.Last Refresh Date Time =DateTimeZone.LocalNow()
This will get refresh only,when you click the refresh button. It is working 100% correct. I'm using in my current project PBI Reports.
