Creating and Displaying a Last Refresh Date Measure

Business users often need to know how fresh the data is that they see on a Power BI report. This requirement can be interpreted to mean either “when was the last date & time that the source data was updated?” or “when was the last date & time that the data model was refreshed?” This method will work with either requirement, but the example will address the latter case.

I’ve used a few different approaches to record the actual date and time, but all generally using the same technique. I will often create a small table in the model containing version information and revision notes, and then add the last refresh date to that table. This is easy accomplished using the Enter Data feature to add rows to the table right in Power BI Desktop. I discovered that often times if records in the utility table weren’t changed or added, the refresh date wouldn’t get updated and it would take more than one scheduled refresh cycle to see the refresh date. Using a column in an actual fact table has proven to be more reliable. If the definition of “last refresh date” is when data at the source was updated, then use a column in the source database table that gets updated in the ETL process or database. If you need to get the last data model refresh, you can use the following technique to create a custom column in Power Query. Regardless, you can create a measure from the appropriate column using DAX.

Here, a custom column is added to the main fact table in Power Query. This records the current date and time every time this table is refreshed in the dataset.

Even if the table is partitioned, returning the MAX value from this column will yield the last refresh date and time.

The measure is very simple:

Using a card, this measure can be placed on any report page to show users how current the data they are viewing is.

Paul Turley

Paul Turley

Microsoft Data Platform MVP, Principal Consultant for 3Cloud Solutions Specializing in Business Intelligence, SQL Server solutions, Power BI, Analysis Services & Reporting Services.

3 thoughts on “Creating and Displaying a Last Refresh Date Measure

  1. Hi Paul, I am not a fan of including in the fact table unless it contains multiple partitions. The reason is the query returns more data as the datetime is added to each individual row of the extract. This is just more bytes to transfer across the network.

  2. I actually have a table in my database called Today_with_Datetime that I load and all it has is the current date and time. That way, any time a refresh is done, regardless of which tables get refreshed, I always have a date. Might be nice to have a table of dates to show the last time each of the tables was refreshed using your technique.

Leave a Reply

%d bloggers like this: