Show report last refreshed time in local time?

[bryanc78]

What's the best way to show the last time a report was refreshed and have that time be the local time, not UTC, after a scheduled refresh?

I've seen multiple solutions but none work once a scheduled refresh occurs.  M code preferred

Thanks!

[Gregory_Felton]

I use this function to adjust UTC to EST in a column. I believe I got some of this from @MarcelBeug

let
ESTAdjustment = (InputDateTimeAsUTC as datetimezone, UTCAdjustmentAmount as number) =>
let
CurrentYear = Date.Year( DateTimeZone.SwitchZone( InputDateTimeAsUTC, UTCAdjustmentAmount ) ),
DaylightSavingsStart = Date.StartOfWeek( #datetimezone( CurrentYear, 3, 14, 0, 0, 0, UTCAdjustmentAmount, 0 ), Day.Sunday ) + #duration( 0, 2, 0, 0 ),
DaylightSavingsEnd = Date.StartOfWeek( #datetimezone( CurrentYear, 11, 7, 0, 0, 0, UTCAdjustmentAmount + 1, 0 ), Day.Sunday ) + #duration( 0, 2, 0, 0 ),
AdjustForDaylightSavingsTime = if InputDateTimeAsUTC >= DaylightSavingsStart and InputDateTimeAsUTC < DaylightSavingsEnd then UTCAdjustmentAmount + 1 else UTCAdjustmentAmount,
AdjustToEST = DateTimeZone.RemoveZone(InputDateTimeAsUTC) + #duration(0, AdjustForDaylightSavingsTime, 0, 0)
in
AdjustToEST
in
ESTAdjustment

[amitchandak]

@bryanc78 , now() in Dax and Datetime.LocalNow() Can help

Please check detailed steps at

https://www.enhansoft.com/how-to-add-the-last-refreshed-date-and-time-to-a-power-bi-report/

[bryanc78]

@amitchandak  This did not work as it kept the time in UTC

[bryanc78]

I was able to get the refresh time added and then subtract the hours needed for my time zone but day light savings time will make it wrong again.

Any other solutions?

[Greg_Deckler]

@bryanc78 - I think use UTCNOW and you can add/subtract the hours. I believe there is an equivalent in Power Query, like DateTime.LocalNow, let me check.

https://docs.microsoft.com/en-us/powerquery-m/datetime-localnow

There is also FixedLocalNow and again, can add/subtract #duration()