Edit

Show last refresh date to Power BI report

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

Adding a last refresh date to a report informs users about the recency of the data presented. You can include a card in the report that displays the date and time when the data was last updated. Additionally, refreshing the data models from Power BI ensures that all data models are up-to-date with the latest information.

The process for incorporating a last refresh date varies depending on the source of your Power BI report, whether it's an Analytics view, Power BI, or an OData query.

Note

Several Analytics entity types, such as WorkItemRevision, WorkItem, WorkItemLink, TestRun, and others, include the AnalyticsUpdatedDate property. This property indicates the most recent time that the individual entity references were updated.

Prerequisites

  • To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
  • To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.

Add the last refresh date based on an Analytics view

To add a column with the last refresh date of the dataset, do the following steps.

  1. Load the Power BI pbix file associated with your view in Power BI Desktop.

  2. In the Queries section of the ribbon, select Transform data > Transform data.

    Screenshot of Power BI Desktop, Home tab, highlighted Transform Data button in Queries section.

  3. Select Advanced Editor.

    Screenshot of highlighted Advanced Editor button.

    If you didn't modify the query, review the following examples with specific table values matching your Analytics view.

     let
     Source = VSTS.AnalyticsViews("{OrganizationName}", "ProjectName}", []),
     #"Private Views_Folder" = Source{[Id="Private Views",Kind="Folder"]}[Data],
     #"{AnalyticsViewsID_Table}" = #"Private Views_Folder"{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data]
 in
     #"{AnalyticsViewsID_Table}"

  4. Modify the query according to the following syntax.

     let
    Source = VSTS.AnalyticsViews("{OrganizationName}", "{ProjectName}", []),
 	#"Private Views_Folder" = Source{[Id="Private Views",Kind="Folder"]}[Data],
 	#"{AnalyticsViewsID_Table}" = #"Private Views_Folder"{[Id="{AnalyticsViewsID}",Kind="Table"]}[Data]
     #"Added Refresh Date" = Table.AddColumn(#"{tableid}_Table", "Refresh Date", 
         each DateTimeZone.FixedUtcNow(), type datetimezone)
 in
     #"Added Refresh Date"

    Important

    These examples use UTC. You can adjust the query code based on your specific timezone as described in DateTimeZone functions.

  5. Select Done.

  6. Select Close & Apply to immediately refresh the dataset.

    Screenshot of Power BI Desktop, Home, highlighted Close & Apply button.

Add last refresh date based on a Power BI or OData query

  1. From Power BI, select Get data > Blank Query.

    Screenshot of highlighted buttons, Get data, and Blank query.

  2. Rename the query to Last Refreshed Date, and then enter the following formula into the function bar.

    Screenshot of Power Query Editor, formula for DateTime.LocalNow for Last Refresh Date query.

  3. To convert the date data to a table format, choose To Table > To Table. Depending on the version you're using, you might need to Invoke parameters first.

    Screenshot of Power Query Editor, To Table option.

    A single column appears with the date.

    Screenshot of converted date column.

  4. From the Transform menu, select the Data Type dropdown menu and select Date/Time option.

    Screenshot of Transform menu, Change Data Type option to Date/Time.

  5. Rename Column1 to something more meaningful, such as Last Refresh Date.

  6. From the Home menu, choose Close and Apply.

    Screenshot of Power BI Desktop, Home, Close & Apply.

Add a card to a report with the Refresh Date

  1. To add a card with the last refresh date to your reports, under Visualizations, choose Card, and add Refresh Date or Last Refresh Date to Fields.

    Screenshot of Power BI Desktop, Card, Refresh Date Applied.

Refresh data

Choose Refresh to refresh report page data and the data model. After all queries are updated, the card refreshes with the latest date.

Screenshot of Power BI, refresh option.