Overcoming the Last Refresh Date Issue in Power BI

Table of Contents

1. Introduction
2. The Issue with Showing Last Refresh Date in PowerBI Reports
3. Adjusting Time Zones and Daylight Saving Time in Power Query Editor
4. Understanding the Difference Between Refresh Start Time and Last Refresh Date
5. Workaround: Creating Calculated Columns for UTC Time with Time Offset Value
6. Creating a Measure to Find the Maximum Value from All Timestamps
7. Comparing Last Refresh Date with Reported Daytime
8. Conclusion

Introduction

In this article, we will discuss how to show the last refresh date and time in PowerBI reports. While it is a common practice to use the "daytime. local now" function in Power Query Editor, there is a major problem with this approach. We will explore this issue in detail and provide a solution to get accurate and reliable results.

The Issue with Showing Last Refresh Date in PowerBI Reports

When displaying the last refresh date in PowerBI reports, there can be discrepancies between the actual refresh time and the reported time. This problem arises due to the difference in time zones between PowerBI service and the local time. We will delve into the details of this issue and understand the reasons behind it.

Adjusting Time Zones and Daylight Saving Time in Power Query Editor

To address the time zone difference between PowerBI service and your local time, you need to adjust the time offset value. This can be done by creating a new calculated column using the time function. In addition, you can also account for daylight saving time if necessary. We will guide you through the process of making these adjustments effectively.

Understanding the Difference Between Refresh Start Time and Last Refresh Date

While analyzing the refresh history, you may notice that the last refresh date displayed is actually the refresh start time. This data set took a certain amount of time to complete the refresh process, but it does not consider this duration. This can be problematic, especially when dealing with large data sets that require significant refresh time. We will explain this difference in detail and its implications.

Workaround: Creating Calculated Columns for UTC Time with Time Offset Value

For those who do not have access to APIs, we will present a workaround solution. By creating calculated columns for UTC time with the time offset value, you can achieve results that closely align with the actual refresh time. We will walk you through the steps of adding these calculated columns to your tables and explaining how they contribute to resolving the issue.

Creating a Measure to Find the Maximum Value from All Timestamps

To accurately determine the last refresh date and time, we will create a measure that finds the maximum value from all the timestamps in the calculated columns. This measure will consider the timestamps from the sales, product, and customer tables. We will demonstrate how to create this measure and utilize it effectively.

Comparing Last Refresh Date with Reported Daytime

After implementing the calculated columns and measure, we will refresh the data and compare the last refresh date with the reported daytime. By closely matching these two values, we can ensure that the displayed last refresh date is accurate and meets our requirements. We will also explore the refresh history page to validate the results.

Conclusion

In conclusion, displaying the last refresh date and time in PowerBI reports can be challenging due to time zone differences. However, by adjusting time zones, incorporating calculated columns, and creating a measure, we can overcome this issue and obtain accurate results. With this workaround, you can ensure that the last refresh date in your PowerBI reports aligns closely with the actual refresh time. Subscribe to our channel for more informative videos on PowerBI and share your feedback and suggestions in the comments section.

Pros

• Provides a workaround solution for displaying accurate last refresh date and time in PowerBI reports.
• Offers step-by-step instructions on adjusting time zones and creating calculated columns and measures.
• Enhances the accuracy and reliability of the last refresh date in PowerBI reports.

Cons

• Requires manual adjustments and additional calculations to overcome the time zone difference.
• The workaround may not capture the exact duration of the refresh process for large data sets.

Highlights

• Discover the problem with displaying last refresh date in PowerBI reports.
• Learn how to adjust time zones and daylight saving time in Power Query Editor.
• Understand the difference between refresh start time and last refresh date.
• Implement a workaround by creating calculated columns for UTC time with time offset value.
• Create a measure to accurately determine the last refresh date and time.
• Compare the last refresh date with the reported daytime for validation.

FAQ

Q: Why is there a difference between the actual refresh time and the reported last refresh date?

A: The difference occurs due to the time zone variance between PowerBI service and your local time.

Q: Can I adjust the time zone and daylight saving time in Power Query Editor?

A: Yes, you can adjust the time zone and account for daylight saving time using the time offset value.

Q: What is the purpose of creating calculated columns for UTC time?

A: Creating calculated columns for UTC time allows you to have accurate timestamps that consider the time offset value.

Q: How can I ensure the accuracy of the displayed last refresh date in my PowerBI reports?

A: By creating a measure that finds the maximum value from all the timestamps, you can closely match the last refresh date with the reported daytime.