Last Refreshed Date for DirectQuery Datasets in Power BI

[PBI_newuser]

I have a report that pulls data from multiple Power BI datasets using DirectQuery. How can I create a measure or retrieve the last refreshed date for each dataset in the report?

[danextian · Accepted solution]

Hi @PBI_newuser 

Are you connecting to semantic models via direct query or connecting to databases via direct query? if the former, you will need to add a table that contains a column on when approximate the refresh happened. In DAX:

RefreshTable =
ROW ( "Refresh", UTCNOW () + DIVIDE ( 8, 24 ) )

Replace 8 with your UTC offset to create a single-row table that reflects your local time after all tables have been imported. Keep in mind that the Power BI service operates in UTC.

For direct query connections to databases, there is no reliable way to achieve this. You can use UTCNOW() + DIVIDE(8, 24) in a measure, but the value will change dynamically as users interact with the visuals. Since the result is cached, navigating back to a previous report state will revert to the earlier time.

If "datasets" refers to individual tables in a semantic model, it is not possible to achieve this. In direct query mode, data updates based on user interactions, so the closest approximation remains UTCNOW() + DIVIDE(8, 24).

[PBI_newuser]

Hi @danextian , a dataset refers to a semantic model. We are connecting to semantic models via direct query. We combined multiple semantic models into one report and now need to display the last refresh date for each semantic model. However, since some semantic models are not owned by me, creating a last refresh date column for each would require reaching out to individual report owners, which is time-consuming. Is there a way to create a measure within the report itself to capture the last refresh date of each semantic model?

[danextian]

I did mention using a measure in my previous reply but that is you're connected to several databases via direct query.

But you cannot rely on measures. NOW, TODAY and the likes are volatile. These measures will update as the user interacts with the visuals and revert to the previous value when a user reverts to the previous state of the report like undoing a slicer selection - this will only confuse the user. You'd want a value that is stored in the memory and will not update until the next refresh.

[rajendraongole1]

Hi @PBI_newuser -

If the datasets you're querying include a last refreshed timestamp column or measure, you can pull that directly into your report. For example:

Check if each dataset has a table or measure containing a last refreshed date, such as [Last Refresh Date].
Add this to your report as a column, card, or table.

Eg: Last Refresh Date = NOW() // Returns the current date/time of the refresh

Hope this helps.

[PBI_newuser]

Hi @rajendraongole1 , there is no "Last Refresh Date" column available in the dataset. Since I pulled data from more than 10 datasets into a single report, do I need to go back to each dataset to create a "Last Refresh Date" field, or is there a more efficient way to retrieve the last refresh date in the DirectQuery report?