Forum Discussion
refreshdate
I want to use refreshdate measure to find out when my model refreshed last time.
I used LastrefreshDate=Now()
the time it shows me ands the time which I get after publishing my report are different. After publishing report in the power Bi service the refreshed shows different time. How can I fix it?
It is probably because the query (that brings the time) was executed once you uploaded the report online.
instead of using DAX, which recalculates when you interact with the report, you need to implement it in power query using the following:
https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow
This will return you the time the query is refreshed. Just note that you need to adjust to your timezone, as this function returns date time in UTC
6 Replies
A measure will always show the date/time of the moment when it was calculated (this means when you open the page in the report). So it is not the time of the refresh, it is time of the visual load on the page.
To get the actual refresh date time, you can follow this video: https://www.youtube.com/watch?v=oN6mOmEruOQ&ab_channel=GuyinaCube
Another thing to remember is that there can be a different timezone between your Notebook and the Service. So you might need to adjust the time by it.It is probably because the query (that brings the time) was executed once you uploaded the report online.
You are doing something wrong.
I took the report in desktop
and published it to the service.
It is the same. There is no reason for it to be different.
This is the code for CEST time.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDLCQAhDAV78Sxonv9axP7bWLNPMN4mDMYhc7oUBAFRmvNOJBTy8r8RnTpNJh8TdRo0iUzT94BIIeTzRBdAaBr5GF0A0FTyMZqGdNM2mwDkG7CZZuhQKEA2ZdWI+pQ1U9ae/7v5v9vTYNzTYNiyFG/Z5rU+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [dtDSTStart = _t, dtDSTEnd = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"dtDSTStart", type date}, {"dtDSTEnd", type date}}, "en-US"),
varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+1)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
varDSTOffset = Table.RowCount(#"Filtered Rows"),
#"Last Refresh Date" = #table(
type table [#"Refresh Date"=datetimezone],
{
{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+1 + varDSTOffset,0)}
}
)
in
#"Last Refresh Date"
my guess is that you perhaps run a refresh after you put the report to service? Keep in mind that the report in desktop and report in service are not interconnected.
instead of using DAX, which recalculates when you interact with the report, you need to implement it in power query using the following:
https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow
This will return you the time the query is refreshed. Just note that you need to adjust to your timezone, as this function returns date time in UTC