Do you want to add the last refresh date in your Power BI reports?

Displaying the last refresh date in Power BI ensures data transparency and trust among users. It communicates the freshness and reliability of the data, empowers efficient decision-making, and assists in monitoring performance and error detection.

This practice enhances user engagement, complies with data governance standards, and provides a clear audit trail, ultimately improving data-driven insights and actions.

In simple terms, it keeps things clear and helps us use data better.

In this article, you will explore different methods to add the last refresh date to your Power BI reports. You will explore Power Query, DAX, WorldTimeAPI, and how to find this information in the Power BI service.

Add Last Refresh Date with Power Query

One way to ensure you have the last refresh date is to create a Power Query that gets the current date and time.

1. Open Power BI desktop and go to the Home tab.
2. Open the Get Data dropdown menu.
3. Choose Blank Query option.

4. Once you click on the Blank Query option, you will get a blank query with the name Query1, Rename it to Last Refresh Date.

= DateTime.LocalNow()

5. Now to the fun part, go to the formula bar and insert the above M code formula.

6. Now you need to change the date to a table. Simply choose the To Table option in the Transform tab.

The resulting table will have one column, you need to follow a few simple steps to make this column ready for use.

7. Change the column’s data type to Date/Time.

8. Change its name to Last Refresh DateTime.

Optionally, if you want to have the Date only and/or Time only. You can add columns that extract the date and time information from the original column.

• To add a column with the Date only, Select the original column, go to Add Columns and open the Date drop-down menu then select the Date Only option.

• To add a column with the Time only, Select the original column go to Add Columns, open the Time dropdown menu then select the Time Only option.

You can now save your changes and load the newly created Last Refresh Date Query into your Data Model.

9. Go to the Home tab and click Close & Apply.

10. You can now use these newly created columns to show your last refresh date/time, perhaps using a card visual.

📝 Note: Once you publish your report to the Power BI service, you might see the time shifting, and this is because the Power BI service might be using a different time zone (UTC).

= DateTimeZone.UtcNow()

There are a few complex ways to fix this, like replacing the formula in step 3 with the above.

= DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),3)

You can then wrap this inside a DateTimeZone Switch like above to adjust to your local time zone.

However, you will have to consider daylight savings as well. It gets more complicated if you have users who are working in different time zones.

By the end of this article, you will have a way that can help simplify this.

Add Last Refresh Date with DAX

The last refresh date can also be added with DAX.

1. Go to the Table view.
2. Create a New Table,
3. Rename the table to Last Refresh Date DAX in the formula bar. This will result in a table with a blank column.

Last Refresh Date = NOW()

4. Click on the New Column button to add a new calculated column. Add the above formula to the formula bar.

Again, this will show the time based on your local machine time zone, it might change once you publish to the Power BI service.

Last Refresh Date = UTCNOW()+(3/24)

You can solve this by replacing the formula with the above, which is switching the UTC time to UTC+3 hours.

5. Add a visual (e.g., Card) to your report and place the Last Refresh Date column there.

Now you have a DAX measure with the last refresh date in your report.

How to Find Last Refresh Date in the Power BI Online Service

Once the report is published on the Power BI service the users can find the last update date for the dataset.

Click on the Chevron icon next to the report name and this will reveal the Date updated.

Get the Correct Date Time from an API

As mentioned before, the Power Query and DAX ways might need some workarounds to get the correct time zone after the report is published.

That’s why I recommend that you use the method below which utilizes WorldTimeAPI. It is a simple web service that returns the current local time for a given time zone. You can get details from this URL https://worldtimeapi.org/.

Steps to use the WorldTimeAPI.

1. Open Power BI desktop and open the Get Data dropdown menu, choose the Web option.

http://worldtimeapi.org/api/timezone

2. First you need to get the last part of the URL which reflects your time zone, so you will need to start by adding the above URL in the From Web window.
3. Click on the OK button.

3. Power Query editor will load a table with all time zones available. You can now search for your time zone. For example (Africa/Cairo). Copy the value to your clipboard. This will be used to edit the source step of the query.

http://worldtimeapi.org/api/timezone/Africa/Cairo

4. Go back to your Source step in this query and modify the URL by adding your time zone at the end of the URL like in the above example.

5. Delete any other steps after the source.
6. Then press the Into Table button to convert the query results into a table.

7. Filter the column Name to keep only datetime, then change the Value column datatype to Date/Time/Zone. Rename the Value column to Last Refresh Date and delete the Name column.

You can now close and apply the query to save it, then you can visualize this through a card visual on your report as seen before.

Additional Notes:

When will the Last Refresh Date Change?

The newly created Last Refresh Date, whether it be from Power Query, Dax, or WorldTimeAPI, will refresh when all the data model is refreshed.

If you refresh single tables in your data model, the Last Refresh Date will not be updated.

However, rest assured when you publish to the Power BI service, the whole model will refresh and the last refresh date will then be updated.

Last Refresh Date for the Dataset and Not the Data Source

All the ways discussed to show the Last refresh date in your Power BI report, will display the refresh date of the Power BI dataset not the last refresh date of the data source.

Consider this scenario, if you are connected to a SQL server and the data hosted in that server fails to be updated for a few days, this will not stop the Power BI dataset from refreshing.

Say the SQL server’s last refresh was on 11th of October and the Power BI dataset refreshes on the 17th of October. The results that you will see from all the methods discussed will show the 17th of October.

Conclusions

Tracking the last refresh date in Power BI is vital for monitoring data accuracy.

You explored different methods. The last refresh date can be added to your reports with Power Query, DAX, or an API.

The Power BI service also makes it easy to find the last refresh date for your published reports, ensuring you’re always working with the most up-to-date data.

Have you added the last refresh date to your reports? How did you do this? Let me know in the comments!