Last refresh date not correct in power bi service

[Anonymous]

Hi All,

I have a query that provides the last time the data was refreshed in power bi desktop (I will post the query below). The query provides the correct date and time when I refresh in power bi desktop. The issues Im having is once I publish the pbix to the power bi service then refresh the data, the time changes by +5 hours. I have not been able to find any solutions that will work with Eastern Standard Time. 

[DLE]

I was looking for the same.

I found that you can write in your SQL source query : SYSTIMESTAMP as LastRefresh. Works fine for me.

[Anonymous]

Hi @Anonymous ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

some time ago Reza Rad published this blog:
https://radacad.com/solving-dax-time-zone-issue-in-power-bi

I guess this provides what you are looking for.

Especially this M function

= DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),12,0)

Hopefully, this provides what you are looking for.

Regards,

Tom

[blopez11]

The service shows time in UTC, so you'll have to adjust based on what timezone you are in, and periodically for day light savings

We've run into this as well

However, with the new look on in the service, it will show the date and time that the report was last refreshed, and in the correct timezone

[Anonymous]

Is there a way to adjust in power bi desktop? Or do I need to adjust on the report on the service?

[blopez11]

We've added the following columns in our table, (we are central time)

CentralTimeOffset is a parameter we created using "Manage Parameters" that we set to 5 or 6 depending on whether we are daylight saving time or not.  Since it is a parameter, we can change it in the service

#"Added Refresh Date Time (UTC)" = Table.AddColumn(#"Changed Type", "Refresh Date Time (UTC)", each DateTimeZone.UtcNow(), type datetimezone),
#"Added Refresh Date Time (CST)" = Table.AddColumn(#"Added Refresh Date Time (UTC)", "Refresh Date Time (CST)", each DateTimeZone.RemoveZone ( DateTimeZone.SwitchZone ( [#"Refresh Date Time (UTC)"], -CentralTimeOffset ) ), type datetime)