Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Polina_Lu
Frequent Visitor

PowerQuery, Calendar, Incremental Refresh, UTC

Good afternoon, colleagues!
Question on Power Query on MS cloud. I create an auto-updated calendar there. To do this, I use a structure like:

 

 

Source = List.Dates(
Date_MaxDate, //start date (max)
Number.From(Date_MaxDate - Date_MinDate) + 1, //count of days as number
#duration(-1,0,0,0))

 

 

Where Date_MinDate is the recorded earliest calendar date
And Date_MaxDate is calculated as 

 

 

Date.From(DateTime.LocalNow())

 

 

 - we will return to this later

T

he calendar is updated in the cloud according to the schedule, taking into account UTC +3 (according to Moscow time)

I know technically the cloud is in western Europe. And there is the time at 21:30 at the time when it is 00:00 in Moscow time, i.e. time lag 2:30.

I need Date_MaxDate to be today at 00:00.

To do this, it is logical to do the calculation

 

 

Date_MaxDate = Date.From(DateTime.LocalNow() + #duration(0,2,30,0))

 

 

or

 

 

Date.From(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),+3))

 

 

 

When checking for both cases, I see, for example, on 08/05/2022 I get that my Date_MaxDate = 08/04/2022

I know what can be done like this and not take a steam bath

 

 

Date.From(DateTime.LocalNow() + #duration(1,0,0,0))

 

 

 

The question is why other options do not work correctly with a shift of +2 hours 30 minutes or with a time zone shift?
Does your practice solve a similar problem so that when you update the calendar (for example, at any time of the current day) to get the correct maximum date?

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Cloud follows UTC not on the basis of it's geographical location. Hence, you have to offset hours on the basis of UTC not West European zone 

View solution in original post

3 REPLIES 3
Zalina
Advocate I
Advocate I

Do you have the full code of how it ended up looking with a time offset?
Would be really helpful as I am trying to figure out the same for CET.
Thank you!

Vijay_A_Verma
Super User
Super User

Cloud follows UTC not on the basis of it's geographical location. Hence, you have to offset hours on the basis of UTC not West European zone 

Thanks!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors