Microsoft Power BI offers some options to display the last refresh time of the data in your dashboard. One of the options provided by Power BI is described here, but it requires many settings to be changed and uses UTC. The latter means that it does not use Amsterdam’s UTC, which is UTC +1 or +2 depending on daylight saving time.
This can be simpler, and in this article, we will explain step by step how you can achieve this.
Step 1: Create a new query
First, a new query must be created. To do this, navigate to the Power Query editor. Then (1) click on ‘New Source’ and (2) ‘Blank Query’.
To fill the blank query with data, enter the following formula:
= #table(type table[Last Refresh=datetime], {{DateTime.LocalNow()}})
The italic part of the formula refers to the title of your column, so it can be changed as desired.
Then change the name of the query by double-clicking on it. In this example, we use the title ‘Last Refresh Time’.
Step 2: Add DAX expression for the time in Power BI
The query generated in the previous step shows the time when the data was refreshed. However, it does not yet take time zones into account. Power BI Desktop uses the correct time zone. When you publish the dashboard to the Power BI workspace, a different time zone is used, causing the time to be displayed incorrectly. This can be solved by using a DAX expression that accounts for daylight saving time as it applies in the Netherlands.
Select the query and add a new column as shown below.
Paste the following formula into the new column:
Last Refresh Netherlands = IF(‘Last Refresh Time'[Last Refresh].[Year]=2022,IF(AND(‘Last Refresh Time'[Last Refresh].[Date]>DATE(2022,3,26),’Last Refresh Time'[Last Refresh].[Date] This formula accounts for daylight saving time until 2024 by checking the dates on which it is in effect. This gives you a column that shows the time with UTC+2 during daylight saving time or UTC+1 during standard time. Note: The formula from step 2 only shows the correct time in the Power BI workspace. If you only need to display the time in Power BI Desktop, step 1 is sufficient. This way, you can add the last refresh time to your dashboard in Microsoft Power BI. Also, check out other articles in our knowledge base. Curious how to easily add a date table in Power BI? Then read here step by step how to achieve this. If you have questions, feel free to contact us.
