Last refresh Time

[BhavyaM]

Hi friends,

please help 

How to display Last refresh time in this format in Card Visual.

“Last Refreshed : 2 mins ago”

mins can be differ based on the refreshed time. Hrs/Secs/ days

Thanks in advance

[edhans · Accepted solution]

You need to create a date in Power Query and pull that in. See this M code.

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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"

That will return a single field with a date. You need to edit the SOURCE row (hit the gear icon) to modify the Daylight Savings Times to your calendar. You also need to modify the -8 offset for the timezone to match your timezone offset to UTC.

Then just create a measure in DAX that would be something like this:

Last Update = "Last Update on " & UNICHAR(10) & FORMAT(MAX('Refresh Time Stamp'[RefreshDate]),"MMM DD, YYYY H:MM AM/PM")

The UNICHAR puts a line break in and shows it on two rows You can remove that.

Note that this will not get the correct hour during the actual DST switch to on/off. I didn't take it that far. It will be correct though after 2am/3am of those days.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[BhavyaM]

So based on the Jobs schedules in services, is this date and time also get refresh and shows me last data set refresh time 

[BhavyaM]

When i publish my report to Power BI service, I am not getting the refresh time as per the Job Scheduled as per the Abu-dhabi time zone

[edhans]

Can you post your M code @BhavyaM - you have to make sure you put in the proper time zone offset. -8 is for Pacific time. I think you need Gulf Time Zone, which is +4.

Then make sure your reports are set to refresh in your time zone.

[BhavyaM]

@edhans 

Hi friend,

I have changed my timezone in the query as below

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}}),
varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+4)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
varDSTOffset = Table.RowCount(#"Filtered Rows"),
#"Last Refresh Date" = #table(
type table
[
#"RefreshDate"=datetimezone
],
{
{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),+4 + varDSTOffset,0)}
}
)
in
#"Last Refresh Date"

 

and Selected Abudhabi timezone as below from the service data set

IST is ahead of 1:30 mins Abudhabi time. But i am getting 30 mins difference.

Could you please help me. 

Thanks in Advance.

[BhavyaM]

@edhans 

Can you please give me solution. Time is not matching exactly.

[edhans]

Try 5.5. There is nothing wrong with my query. You just need to find the time zone offset from UTC. Everything in the Power BI service runs on UTC time. The TimeZone functions let you move forward and backwards from that to your local timezone. If the below doesn't work, use Google to find your time zone and how many hours it is ahead (positive number) or behind (negative number) and put that into this query.

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}}),
    varCurrentDate = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), 5.5)),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [dtDSTStart] < varCurrentDate and [dtDSTEnd] > varCurrentDate),
    varDSTOffset = Table.RowCount(#"Filtered Rows"),
    #"Last Refresh Date" = #table(
        type table
            [
                #"RefreshDate"=datetimezone
            ],
        {
            {DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), 5.5 + varDSTOffset,0)}
        }
        )
in
    #"Last Refresh Date"