Last Data refresh Date

[Manikanta2108]

Instead of publish,semantic refresh date. i need to show date for latest changes of datasource in dashboard. How can i do it ?

[Cookistador · Accepted solution]

Hi @Manikanta2108 

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
    Source

Then 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

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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:

1. For SQL Server or Azure SQL, please use the following query:
   SELECT MAX(LastModifiedDate) AS LastDataUpdate
   FROM YourFactTable;
   
   
2. 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
   
   
3. 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.

[danextian · Accepted solution]

Hi @Manikanta2108 

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.

[Ritaf1983]

Hi @Manikanta2108 

Please refer the linked video:
https://www.youtube.com/watch?v=oN6mOmEruOQ&t=2s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Cookistador · Accepted solution]

Hi @Manikanta2108 

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
    Source

Then 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