Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Does anyone have an idea for a dax measure to find out when our database was last refreshed and when our power bi report was last refreshed. There are times when database gets refreshed but power bi report does not get refreshed due to some reasons such as timeout errors.
Solved! Go to Solution.
hi @Pankhuri_16
Step1: You can use GetDate function in SQL Server and store that date only after all necessary tables have refreshed.
Step2: Capture date during Power BI refresh, please note that by default when you refresh your dataset on Power BI service, it returns datetime in UTC, so it may differ from your date time captured on SQL Server. You will need to make sure Date returned by Power BI service is per your timezone by converting it(Check Timezone functions in PowerQuery).
How do we store database refresh date in any table on sql server ?
hi @Pankhuri_16
Step1: You can use GetDate function in SQL Server and store that date only after all necessary tables have refreshed.
Step2: Capture date during Power BI refresh, please note that by default when you refresh your dataset on Power BI service, it returns datetime in UTC, so it may differ from your date time captured on SQL Server. You will need to make sure Date returned by Power BI service is per your timezone by converting it(Check Timezone functions in PowerQuery).
To create a DAX measure to find out when your database was last refreshed and when your Power BI report was last refreshed, you can use the following approach:
Database Refresh Date:
Power BI Report Refresh Date:
Here's an example of how you can create a DAX measure to find out the last refresh date/time for both the database and the Power BI report:
DatabaseLastRefreshDate = MAX('DatabaseRefreshTable'[RefreshDate])
PowerBILastRefreshDate = MAX('PowerBIRefreshTable'[RefreshDateTime])
Replace 'DatabaseRefreshTable' and 'PowerBIRefreshTable' with the actual names of your tables where you store the refresh date/time information for the database and Power BI report respectively.
Ensure that you have a column in your 'PowerBIRefreshTable' that captures the refresh date and time whenever your report is refreshed. This column can be populated using a datetime variable created in Power Query as mentioned earlier.
With these measures in place, you can easily track when your database and Power BI report were last refreshed and compare them to identify any discrepancies.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |