Skip to main content

Data & Intelligence

How to Show Last Refresh Date in Power BI

Featuredimage

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.

Image1

Step 2: Under the Home ribbon, click on the New Source option and select Blank Query.

Image22

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.

Image3

Afterward, go to the formula bar, write below the M query, and press enter.

= DateTime.LocalNow()

Image4

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.

Image5

When you click on the To Table option, you will see it is converted into a single column.

Data Intelligence - The Future of Big Data
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.

Get the Guide

Image6

Step 4: Now, Change the Data type to Date/Time, and to save all those changes, click on the Close & Apply tab.

Image777

In the field section, you can see a table that returns the Last Refreshed Date.

Image8

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.

Image9

Step 6: We need to refresh the Data page or Data model, so we will click on the Refresh tab.

Image12

After refreshing the data, we will get the latest refreshed date and time accordingly, the same as my system date and time.

Image13

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.

Image14

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.

Image15

Then select both the visuals using the ctrl button, right-click on any of the visuals, and group them.

Image16

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.

Image17

 

Thoughts on “How to Show Last Refresh Date in Power BI”

  1. 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?

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

  3. 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?

  4. 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 🙂

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

  6. 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)

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

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

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

  10. Fairos Abdul Mutalip

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ashwini Neware

Ashwini Neware is an Associate Technical Consultant at Perficient based out of Nagpur. She is a Microsoft Certified Power BI Data Analyst Associate with over 3 years of experience in Power BI.

More from this Author

Follow Us