Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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/
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()
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.
@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".
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
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()
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
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |