Showing the last refresh date and time in a Power BI report let you know how to refresh your report data, as sometimes the report viewer can not remember the last date and time when report data was refreshed. Just in case, when you never refresh the report, Power BI keeps showing existing data until the report is not refreshed; therefore, it will be better to add the last refresh date in the Power BI report.
In this blog, we will see how to add the last refreshed date in the Power BI report. Even this can also be used to see whether your automatic refresh is working fine or not.
Steps to add the “Last Refresh Date”:
Step 1: Open the Power BI desktop, and click on the Transform Data option.
Step 2: Under the Home ribbon, click on the New Source option and select Blank Query.
Step 3: Once you have Blank Query table Query1 in place under the Queries section, right-click on it and rename it to Last Refreshed Date.
Afterward, go to the formula bar, write below the M query, and press enter.
= DateTime.LocalNow()
Now we will convert this date data into a table format. For this, we need to right-click on the To Table tab and select the To Table Option.
When you click on the To Table option, you will see it is converted into a single column.
The Future of Big Data
With some guidance, you can craft a data platform that is right for your organization’s needs and gets the most return from your data capital.
Step 4: Now, Change the Data type to Date/Time, and to save all those changes, click on the Close & Apply tab.
In the field section, you can see a table that returns the Last Refreshed Date.
Step 5: For the analysis, let’s take a card visual on the canvas and drag the Last Refresh Date column into visual.
You can see the last refreshed date is 18-08-2022.
Step 6: We need to refresh the Data page or Data model, so we will click on the Refresh tab.
After refreshing the data, we will get the latest refreshed date and time accordingly, the same as my system date and time.
Notice that creating a column with the name Last refresh date acts like a title on the card. In my last project client had already provided this card data in a single line in their template at the bottom of every page, which is the best way of showing the last refreshed date.
Let’s create the same scenario. For that, we need to add a text box. The text box acts as the title of the card. On the other hand, the card only displays the last refreshed date and time details.
After selecting the text box from the Home ribbon, enter the text “Last Refreshed Date:” and pull the card next to the text box as shown in the above image.
Once you have both the text box and card on the canvas, you can customize them as per your requirements. I adjusted the font size, and font color, and I turned off the category label from the format tab under the visualization pane.
Then select both the visuals using the ctrl button, right-click on any of the visuals, and group them.
Finally, I re-sized both the text box and card in order to fit them in the bottom right-hand corner of the page. Instead of adding a calculated measure to a card, this method gave me more area for my chart.
Useful information . Nicely articulated as well.
Thank you Arpit.
Thank you for this info, pretty much exactly what I was looking for.
I do have a question though, I have my reports pulling data in from SQL. Now if the SQL refresh fails, but this “Last Refreshed Date” runs, then the report will still say its updated, even though it isn’t. is that correct?
Yes, you are right Chris, when you are saying data within the SQL DB was update then you are assuming that data is static and update on a schedule or manual. So if refresh fails on data side it means it will not affect on report’s data and it will still show you updated date and time without affecting anything.
Thank you for providing this!!
Hi, I’ve used this solution to create a refresh date time stamp however the time on the card visual is not in the same time zone when I publish to Power BI service. It is correct in Power BI desktop, wondering why that is and how to fix?
Also, just clarifying if we have data from SQL server the last refresh time in this card visual doesn’t necessarily reflect the last refresh date of the dataset?
Hi Ashwini,
That’s a great article. The DateTime.LocalNow() function retrieves the current time on the machine that refreshes the Power BI dataset.
That can be your laptop, but when you upload a dataset to the power BI service it can also happen from another country. That’s all depending on the country your tenant is in.
For example, my tenant is in Ireland, even though I work in the Netherlands.
In those cases the last refresh date can be wrong. One suggestion if you’re experiencing that issue is to include a daylight saving time.
You can do that by offsetting the UTC value from DateTimeZone.UTCNow() by the relevant offset values for summer and wintertime a country has.
Some more details are written here:
https://gorilla.bi/power-query/last-refresh-datetime/
Hope that also answer Justine’s question 🙂
Very well explained and very usefull! Thank you very much!
My current time : 8:45am and my last refresh time 12:06. I am from Malaysian which is UTC +8. How to change the last refresh time to my local time which is 8:45am
Hi Harsh, if you want to see preferred Timezone you need to change the M code within the query editor. SwitchZone function will help you to get the expected results.
You can try this code :
Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),+8,0)
Hi,
How can I use this field in the DATEDIFF Dax?
I want to show the percentage of days that has gone by for a certain period.
this is okay for power bi desktop. But when i upload the report to the service the date is not what it should be. not sure why
Hi, This solution only gives a fixed time which is in UTC but is confusing for teams which are located outside of UTC.
Have below requirement which also makes sense for teams located across the globe.
– Either provide zone info along with time, or
– Need to display user local timing as shown in Power BI dashboard.
Any suggestion please share. Thanks in advance
If the date in Power BI Service shows UTC time, then one need to change to local time. The use of Switch function is useful to show local time.
My Sharepoint Server is located in Cloud in Oslo, Norway hence it reflects UTC Time. Therefor i use the switch function to reflect Local Time.