Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pankhuri_16
Frequent Visitor

Database refresh date and power bi refresh date

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. 

1 ACCEPTED 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). 

View solution in original post

3 REPLIES 3
Pankhuri_16
Frequent Visitor

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). 

123abc
Community Champion
Community Champion

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:

  1. Database Refresh Date:

    • Assuming your database refresh date is stored in a table in your database, you can create a DAX measure to retrieve the maximum or latest refresh date from that table.
  2. Power BI Report Refresh Date:

    • Power BI does not have a built-in feature to directly access the refresh date/time of a report. However, you can use a workaround by creating a datetime variable in Power Query that gets the current date and time when the report is refreshed. Then you can use this variable to track the last refresh time.
    • To create a datetime variable in Power Query, you can use the DateTime.LocalNow() function. This function returns the current date and time in the local time zone.
    • Once you have this datetime variable, you can store it in a table or use it directly in a DAX measure.

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.

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.