cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mterry
Helper IV
Helper IV

Time Zones and Scheduled Refresh

I created a table to show the latest refresh day and time for my reports, had issues when I scheduled a refresh through the Power BI service, and reading through here found the cause of the error (I believe). In the code below the LocalNow() portion is using UTC time when the refresh gets scheduled through the service whereas it's using my time on my local machine through the desktop app. 

 

= #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})

 

So I updated my table to pull the UTC date & time, then added a column that adjusted the time zone. However, I'm still having issues, and I don't understand why, code is pasted below. If I'm using the Utc time as an anchor, why is my 'LastRefreshLocal' column still pulling UTC time instead of switching to my local time zone?

 

let
Source = #table(type table[LastRefresh=datetimezone], {{DateTimeZone.UtcNow()}}),
#"Added Custom" = Table.AddColumn(Source, "LastRefreshLocal", each DateTimeZone.SwitchZone([LastRefresh],-6,0)),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"LastRefresh", "LastRefreshUTC"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"LastRefreshLocal", type datetimezone}})
in
#"Changed Type"

 

1 ACCEPTED SOLUTION
blopez11
Memorable Member
Memorable Member

Yes, I have the value displaying on my reports so the user knows when the data was last refreshed

View solution in original post

6 REPLIES 6
blopez11
Memorable Member
Memorable Member

Here are the M steps that I used that seem to work fine (CentralTimeOffset is a parameter)

Though they don't look much different to what you are doing

Good Luck

 

#"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)

Hi , please help me these,

instead of CentralTimeOffset which number values will gives exact sheduled refresh date time through server and how to add that time in power bi report

Likhitha6417_0-1637911970162.png

in M code Centaltimeoffset is Contains not correct please help me for these

 

Okay thanks. And this works for scheduled refreshes through the Power BI service?

blopez11
Memorable Member
Memorable Member

Yes, I have the value displaying on my reports so the user knows when the data was last refreshed

View solution in original post

That worked, thank you!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!