Forum Discussion
Last Data refresh Date
Instead of publish,semantic refresh date. i need to show date for latest changes of datasource in dashboard. How can i do it ?
In power query, you create a new blank query, then you click on advanced editor in the home ribbon and you paste this code instead of the source one:
let Source = DateTime.LocalNow() in SourceThen you just have to convert it to a table:
Rename the query with something like LastRereshDate, close and apply and now, you are able to get the last refresh date in your report
If you prefer, you can also create a table in DAX and use the NOW() function but you have to create a calculated column, if you use it with a measure, each time you open the report, the date will be updated
Create a blank query with the following script.
let Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8), #"Converted to Table" = #table(1, {{Source}}), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type datetime}}) in #"Changed Type"Replace 8 with your actual GMT offset. My timezone is GMT+8. This approach takes into consideration that you will be refreshing the semantic model in the service which uses GMT and thus the need to to use DateTimeZone.UtcNow() and then convert it to the local timezone. Please note that Power BI doesn't have a built-in function to check for daylight savings.
If you're referring to when your data itself upon refresh, it depends on the data source. You can use the latest timestamps in the data or if flat files saved in sharepoint the creation or modified date.
7 Replies
In power query, you create a new blank query, then you click on advanced editor in the home ribbon and you paste this code instead of the source one:
let Source = DateTime.LocalNow() in SourceThen you just have to convert it to a table:
Rename the query with something like LastRereshDate, close and apply and now, you are able to get the last refresh date in your report
If you prefer, you can also create a table in DAX and use the NOW() function but you have to create a calculated column, if you use it with a measure, each time you open the report, the date will be updated
Please refer the linked video:
https://www.youtube.com/watch?v=oN6mOmEruOQ&t=2sIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Create a blank query with the following script.
let Source = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8), #"Converted to Table" = #table(1, {{Source}}), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type datetime}}) in #"Changed Type"Replace 8 with your actual GMT offset. My timezone is GMT+8. This approach takes into consideration that you will be refreshing the semantic model in the service which uses GMT and thus the need to to use DateTimeZone.UtcNow() and then convert it to the local timezone. Please note that Power BI doesn't have a built-in function to check for daylight savings.
If you're referring to when your data itself upon refresh, it depends on the data source. You can use the latest timestamps in the data or if flat files saved in sharepoint the creation or modified date.
Thankyou, Cookistador, Ritaf1983 , danextian for your responses.
Hi Manikanta2108,
We sincerely appreciate your query posted on the Microsoft Fabric Community Forum.
Based on my understanding, the current query returns the dataset’s last refresh time, that is, the semantic refresh time in Power BI, rather than the actual last modified date or time of the source data. Power BI does not automatically detect changes in the source data; therefore, the last modified timestamp must be obtained directly from the source metadata or from a specific timestamp column.
To retrieve the date of the last data modification from the source, please follow the appropriate approach depending on your data source type:
- For SQL Server or Azure SQL, please use the following query:
SELECT MAX(LastModifiedDate) AS LastDataUpdate
FROM YourFactTable; - For SharePoint or OneDrive, please use the following Power Query code:
let
Source = SharePoint.Files("https://YourSiteURL", [ApiVersion = 15]),
Filtered = Table.SelectRows(Source, each ([Name] = "YourFile.xlsx")),
LastModified = Table.SelectColumns(Filtered, {"Date modified"})
in
LastModified - For local folder files, please use this Power Query script:
let
Source = Folder.Files("C:\DataFolder"),
Filtered = Table.SelectRows(Source, each ([Name] = "YourFile.xlsx")),
LastModified = Table.SelectColumns(Filtered, {"Date modified"})
in
LastModified
After retrieving the timestamp, load it into the model and display it using a Card visual. This will always show the actual source data change date after refresh, rather than merely the dataset refresh time.
We hope that the above information will help you resolve the issue. Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.
Thank you.
- For SQL Server or Azure SQL, please use the following query:
Hi Manikanta2108,
We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.
Thank you.Hi Manikanta2108,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.Hi Manikanta2108,
We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.
Thank you.
