Code for Last Refresh Date/Time (EST)

[Anonymous]

Hello All

I have the following m-code to show my last refresh date. When published in Power BI Service it shows 4 hours ahead (UTC). I need it to show -4 hours so that EST is reflected. Can anyone tell me how to modify the below to show the -4?

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "DateTime", "DateTime - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"DateTime - Copy", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Date - Copy", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date - Copy"}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Removed Columns", "DateTime", "DateTime - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column2",{{"DateTime - Copy", "Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Time", type time}})
in
#"Changed Type2"

[v-jingzhang]

Hi @Anonymous 

You could create a calculated table with below codes. A calculated table is evaluated when it's created or the dataset is refreshed. After published to service, NOW() will always return a UTC datetime value. So we can minus 4/24 day from that to get a datetime value which is 4 hours behind UTC time.

RefreshTable = {NOW() - 4/24}

Or you could use below M codes. Substract 4 hours duration from the UTC time.

let
    RefreshDateTime = DateTimeZone.FixedUtcNow() - #duration(0,4,0,0),
    #"Converted to Table" = #table(1, {{RefreshDateTime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh Date Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh Date Time", type datetimezone}})
in
    #"Changed Type"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[edhans]

You need to use something along the lines of this, which will adjust the time of the report when it loads.

DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-4)

DateTime.LocalNow that you used is the system time, but the service is always at UTC time.
You might want to check out this article I wrote @Anonymous that will help you add a refresh time to your report using this methid, including a daylight savings offset - Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics

[Anonymous]

Thanks----I changed everything to the following code showing -4 to account for EST. However when I published to the Power BI Service, it still changes it back to UTC time. Any thoughts on how I can resolve this, see below:

let
Source = DateTime.FixedLocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh", type datetime}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date),
#"Insert Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Last Refresh]), type time),
#"Added Custom" = Table.AddColumn(#"Insert Time", "Local Timezone", each DateTime.AddZone( [Last Refresh], -4), type datetimezone)
in
#"Added Custom"

[edhans]

You didn't use the timezone function. All you did was add a -4 timezone to your current time. 

Go back and change the source to what I showed you earlier. It attaches a timezone at that time and the service properly interprets it once it is in DAX. It must be a datetimezone type - which you do have.

I would get rid of ALL of that code - no need for 8-9 steps to get the time stamp on your report. And I would not include all of those other fields - Date, Time, etc. Those will come in wrong too depending on when the report refreshes - the day could be off a day for example since it is based on the refresh and not static dates from a sales table as an example. Once it is in DAX you can just format a measure to show Date or Time only. Or reconstruct a date that is properly TZ adjusted using the DATE() or TIME() functions.

Go click on the article I showed you. It has 100% of what you need and it works. I've been using that for years.