Issue with Last Refresh Date/Time in Power BI
Вставка
- Опубліковано 11 вер 2024
- If we create a query inside Power query editor with DateTime.LocalNow() function to get Last refresh date/time, then there is a very big problem. This almost gives the refresh Start time, because this individual query is very smalls. And if your dataset takes much time to complete it’s refresh, then result of above query is not much useful. Til now we were looking at the TimeZone conversion, but this particular problem is mostly overlooked.
In this video We will try to solve this issue using some tricks.
This video will answer below query-
- How do I get the last refresh data in Power BI?
- How do I check my Power BI refresh history?
- How do I add refresh time in PBI?
- How to fix issue with last refresh time in power bi
- Power BI last refresh date on report
Get Help into Questions Related to #powerbi, #dax, #powerquery Power BI #datamodeling, #analysisservices and #powerbiservice and #microsoftfabric
Check out our website www.powerbihelpline.com and blog-post. for end-to-end power BI solutions, Power BI templates, or any kind of Power BI questions like Power query errors, Authoring and optimizing DAX codes for complex business logic.
Follow us on LinkedIn
/ power-bi-helpline-22a2...
Email- info@pbihelpline.com
@PowerBIHelpline
Interesting approach, but look at this:
= #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()-#duration(0, -1, 0, 0)}})
Last part is actually the only part where you need to adjust according to your Location. And it has exact "timing" in Service.
We struggled a lot with this discrepancies but since I manage to solve it, it gives quite correct time.
Did you notice if it shows a time which is very near to refresh start time ? The whole idea about this video is not about Time Zone conversion, but it is about refresh end time.
In a query like above which is an individual small query, will refresh very fast, and will return the time of its own refresh time, not the time of the entire dataset which may take several minutes.
Good workaround.But if i use incremental enabled XMLA manual refresh for historical partition i might not be able to see the latest refresh as this is getting max refresh of the latest partition.Anyway for most cases your solution might suit.
Absolutely right! The most accurate way is to fetch the refresh history page from the service. and the only way there is to use API.
Thanks for watching.
I think the correct approach is to display the start time, not end time. So there is no issue.
Yes, you can use that where Refresh time is not much important. However, Power BI Service always shows Refresh end date/time in "Refreshed column " of the dataset or "Data Updated on" in report's detail section shows refresh end date/time, not the start time
@@PowerBIHelpline The service is wrong in my opinion. It should be the start time. Because the data included in the dataset ends at the start time.
I understand what exactly you are trying to convey, You are actually talking about the data available in the actual data-source where Power Bi is connected with. and that will definitely pull whatever is available at its start time. but Power Bi has nothing to do with your actual data is refreshing or not in the data source system. even it doesn't update , next day power BI refresh start time will show new date time. so that is a different case all together where we apply other technique. like storing a timestamp as a column in the actual data source itself, and then show that value in the report.
you can use pbi rest api to get correct refresh times
He mentioned that.
How to handle day light savings time?
That can be done either inside power query or in DAX.
where you need to dynamically find the date of last Sunday of march or 2nd Sunday of March depending upon your region.
then validate that date with your result date. and if your result date falls after that date then add 1 extra hour