Hi everyone, I was watching a video from Will Thompson and he mentioned a work around for showing the date of refresh, however he didnt show how he did it. He are the steps: In the desktop PBI click on get data and select Blank Query. This will open up the Query editor. On the right hand side change the Name from Query1 to Last refresh date. In the formula bar enter the below code: = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow()) Click close and apply. You will notice on your fields tab you will have a new item named Last refresh date. You now need to enter in a measure: Last refresh on = "Last refreshed on: " & FIRSTDATE('Last refresh date'[Last refresh date]) No click on the card visual and insert the new measure Last refresh on. You can now see when the dashboard was last refreshed and what time it was done. Thanks, Giles

Had issues with all these approaches as I wanted the last refresh time to always be consistent and relatable for my viewers (i.e. put it in PST all the time). Wanted to make sure this was consistent - no matter if the update ran from PowerBI desktop or if via online Scheduled Refresh. To bypass the issues with local time/server time via M - I found it easier to use the (awesome) web scraping capability of PowerBI to pull the data from a third party source. Below are the steps and code to have a "Last Refresh Tile" that is "Last Refresh PST" (this can be whatever zone you want). Steps: 1) Create a new blank query via "Get Data". 2) Once in the blank query, go to the Advanced Editor 3) Drop in the code below - overwriting the placeholder items in there. This will give you PST Time - so if you want something else, step through the query and you will see the options in the table for other zones. let// get the data from a stable source in table format from the web Source = Web.Page(Web.Contents("http://www.timeanddate.com/worldclock/")),//PowerBI does automatic detection Data0 = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),//navigate to the column that has the time zone that is appropriate for the users of the dashboard (in this case, PST time zone Seattle) #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column8", "Column9"}),//filtered the column to just have PST time shown (select whatever you require) #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column8] = "Seattle*")), #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Column9"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column9", "Last Refresh (PST):"}})in #"Renamed Columns" 4) Close and Load - you will see a query with a column with the current PST time - "Last Refresh (PST)". 5) Once here, you can tweak your format and visualization - use a table for an easy first step. 6) Once up in your report, to enable automatic refresh, in settings for the dataset in PowerBI, use Anonymous as your credential for the website. Done - no matter if you refresh from PowerBI desktop or via automatic refresh, it is "Last Refresh PST".

Here's another approach that works for me I picked one of my small lookup tables and added a custom column using Query. Refreshed = DateTime.LocalNow() I then formatted that new column to remove seconds and put the Month letters in rather than 05 I then used that column in a Card visualisation - all done. No need for a measure.

AndrewDang Not that I can see. Due to the query for the timestamp being a stand alone item it appears to act like a table, therefore it needs refreshing individually. I found a fix to make the time displayed account for +/- GMT. = DateTimeZone.ToLocal(DateTime.AddZone(DateTime.LocalNow(),-10)) The -10 is there because I am actually +10 GMT/UTC time however when I put in 10 it gave me a future date. Essentially if you are positive GMT/UTC put a negative number and vice versa. Thanks, Giles

Came across this on the Power BI Service Click on the ... in a dashboard tiileselect the pencil to edit, and pick Display last refresh. Only works for Dashboards not reports though

Thanks for sharing this everyone I found that just using Query to add a custom column to one of my existing queries worked nicely: Plus I just used =DateTime.Time(DateTime.LocalNow()) and then formatted the column as Date Time

Set up a tile with last refresh date and time

22 Replies

wynhopkins
Most Valuable Professional
10 years ago
Here's another approach that works for me

I picked one of my small lookup tables and added a custom column using Query.

Refreshed = DateTime.LocalNow()

I then formatted that new column to remove seconds and put the Month letters in rather than 05

I then used that column in a Card visualisation - all done. No need for a measure.
- GilesWalker
  Skilled Sharer
  10 years ago
  wynhopkins - Thanks for posting how you have done this. Adding a custom column with Query is excellent.
  
  Are you manually updating your reports, or is this through direct query/auto refresh?
  
  Thanks,
  
  Giles
  - wynhopkins
    Most Valuable Professional
    10 years ago
    Manually updating currently
    
    I'll set up a automated refresh on Monday and see what happens to the date then
- electrobrit
  Post Patron
  9 years ago
  wynhopkinsThis almost works but it does a count when i put it in the card. What am I doing wrong?
  - wynhopkins
    Most Valuable Professional
    9 years ago
    Hi Electrobit
    
    If you drag a date field into a card you will get a count of date
    
    You will need to create a measure to drag into a card, such as =Max(Sales[Date])
    
    Dragging that measure into a card will show the latest sales date in that Card
    
    The method I referred to earlier in this thread was about adding the refresh date to a tile in a dashboard in Power BI.com
    That is is a different approach to the one I mention here
wynhopkins
Most Valuable Professional
10 years ago
Thanks for sharing this everyone

I found that just using Query to add a custom column to one of my existing queries worked nicely:

Plus I just used =DateTime.Time(DateTime.LocalNow()) and then formatted the column as Date Time
- wynhopkins
  Most Valuable Professional
  10 years ago
  Wierdly I just tried this with a different Power BI Desktop file and just using =DateTime.Time(DateTime.LocalNow()) doesn't give me the correct date (gives 1899 date i.e. 0) which I guess is the behaviour you'd actually expect. Not sure how the other file is doing it?
  
  Follow up note: See solution below (must have been due to placing the extra DateTime.Time at the start whcih isn't necessary. Just used =DateTime.LocalNow()
GilesWalker
Skilled Sharer
10 years ago
One thing I forgot to mention; if you are using the automatic refresh function the time in this formula will convert to US time. You need to enter this formula in the M function step with the added or minus hours you require:

= DateTime.AddZone(DateTime.LocalNow(),+10)

Thanks,

Giles
- GilesWalker
  Skilled Sharer
  10 years ago
  For some reason the M formula to adjust time zone issues does not seem to work. I still just get the time of at GMT not with the plus 10 for my time zone.
  
  Does anyone know how to fix this?
  
  THanks,
  
  Giles
  - AndrewDang
    Helper IV
    10 years ago
    Thanks GilesWalker
    
    This is a great tip to create the last refreshed date. I have a quick question, though. My refresh date tile does not seem to refresh when I refresh data on other tables. I have to click Refresh Data on this table to get the date stamp. I am wondering if there is a quick way to link the two so that it will automatically refresh the date whenever I refresh data on other tables?
    
    Thanks;
    Andrew
- Anonymous
  Not applicable
  7 years ago
  I don't seem to have datetime function in my version of powerBI. Could you please suggest something else?
acp8888
Frequent Visitor
9 years ago
I have Power BI connected to an SSAS cube and want to track a log of update times. I have a field created to give me the last updated time but does anyone know of a way to track this information historicaly to analyze the time it takes Power BI to update from my cube?
benjaminhoang
Helper III
8 years ago
I mixed your = DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())

with the solution from this website and it is working perfectly for me with the correct time once uploaded to power bi service which we stream our dashboards in the office.

https://www.fourmoo.com/2017/03/28/power-bi-create-last-dataset-refresh-date/

Forum Discussion

Set up a tile with last refresh date and time

22 Replies

Related content

Dashboard Tiles Not Refreshing in Embedded UI — DirectQuery / Embed API

updating one tile based on selection in another tile

REAL TIME TILES AUTO CONVERT TIMESTAMP TO UTC

Refresh dataset api

Increment Refresh