How To Add Last Refresh Time In Power Bi

How to Add Last Refresh Time in Power BI

Power BI does not have a built-in feature to display the last refresh time in the report or dashboard. However, you can achieve this by using a combination of Power Query and Power Automate (previously known as Microsoft Flow).

Step 1: Get the last refresh time using Power Query

Open your Power BI Desktop and go to the “Home” tab.
Click on “Edit Queries” to open the Power Query Editor.
In the Power Query Editor, click on “New Source” and then select “Blank Query”.
Open the “Advanced Editor” by clicking on the “Advanced Editor” button in the toolbar.
In the Advanced Editor, paste the following code:


let
    Source = #datetimezone(DateTime.LocalNow(), DateTimeZone.Local)
in
    Source

Step 2: Load the last refresh time to Power BI

Click on the “Close & Apply” button in the Power Query Editor to apply the changes and load the data.
In Power BI Desktop, go to the “Home” tab and click on “Edit Interactions”.
Select the table or visual where you want to display the last refresh time.
Go to the “Visualizations” pane and click on the “New measure” button.
Enter a name for the measure, for example, “Last Refresh Time”.
In the formula bar, enter the following DAX formula:


Last Refresh Time = MAX('QueryName'[DateTimeColumnName])

Note: Replace ‘QueryName’ with the appropriate name of your Power Query and ‘DateTimeColumnName’ with the column name where you loaded the last refresh time.

Step 3: Format and display the last refresh time

Select the table or visual where you added the measure.
Go to the “Visualizations” pane and choose the appropriate visual type (e.g., Card, Textbox).
Drag and drop the “Last Refresh Time” measure into the value or text field of the visual.
Format the visual as desired, such as changing the font size or adding a title.

Step 4: Automate the refresh and update of last refresh time (optional)

If you want the last refresh time to update automatically whenever the dataset is refreshed, you can use Power Automate (Microsoft Flow).

Go to powerautomate.microsoft.com and sign in with your Microsoft account.
Create a new flow by clicking on the “My flows” tab and then “New” and select “Automated – from blank”.
In the flow editor, search for the “Power BI” connector and select the trigger “Power BI – When a data-driven alert is triggered”.
Choose the appropriate workspace, dataset, and alert rules for your report.
Add an action to update the last refresh time in your dataset by using the “Power BI – Update dataset” action.
In the update action, specify the dataset name, table name, and column name where you want to update the last refresh time.
Save and test your flow, and make sure it is triggered correctly when the dataset is refreshed.
After the flow is set up, the last refresh time in your Power BI report will be automatically updated whenever the dataset is refreshed.

Example:

Let’s say you have a dataset named “SalesData” with a table named “Sales” that contains a column “LastRefreshTime”.

In Power Query, you generate a new table with the last refresh time using the code mentioned in Step 1.

After loading the data, you create a measure in Power BI with the DAX formula mentioned in Step 2:


Last Refresh Time = MAX('SalesData'[LastRefreshTime])

You add a card visual to your report, select the “Last Refresh Time” measure, and format the visual as desired.

If you choose to automate the refresh and update of last refresh time using Power Automate, you set up the flow as described in Step 4. Whenever the dataset is refreshed, Power Automate updates the “LastRefreshTime” column in the “Sales” table, and it reflects in your report.

How To Add Last Refresh Time In Power Bi