cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker
Skilled Sharer
Skilled Sharer

Set up a tile with last refresh date and time

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

22 REPLIES 22
benjaminhoang
Helper III
Helper III

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/

acp8888
Frequent Visitor

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?

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() 

 

Refreshed Column.PNG

 

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

Date Format.PNG

 

I then used that column in a Card visualisation - all done.  No need for a measure.

 

Refreshed Card.PNG

 

 

 

@wynhopkinsThis almost works but it does a count when i put it in the card. What am I doing wrong?

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

 

 

 

 

 

Thank you, your approach still worked. After I did the custom column "last refresh1". I added a measure

Last Refresh = Max (Table{last refresh1}) and that worked!

 

 

@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

Manually updating currently

 

I'll set up a automated refresh on Monday and see what happens to the date then

 

 

@wynhopkins - will be interested to see how you go with the automatic refresh as this casued me issues with the Date.LocalNow equation because it takes the time at the servers which is UTC time, i.e. GMT 0.

 

 

I set up an autorefresh with an Excel file on my desktop as a data source

 

Date and time looked fine.

 

I guess if you're pulling the data from an online source that is located elsewhere then you may well  have issues.

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".

Thanks for this insight very helpful! 


UPDATE: https://www.timeanddate.com/worldclock/

 

Hi @Virtual_Ames, your solution worked for me, thank you so much for this. 

Came across this on the Power BI Service

 

Click on the ...  in a dashboard tiile

select the pencil to edit, and pick Display last refresh.  Only works for Dashboards not reports though

 

Refresh.PNG

 

 

I have successully applied this to my dashboards. Thanks for the tip.

 

However pne dashboard does not give me the option of showing the last refresh data and time within  "Edit details". Any ideas as to why?

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

 

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
Skilled Sharer

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

I don't seem to have datetime function in my version of powerBI. Could you please suggest something else?

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors