cancel
Showing results for
Did you mean:

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

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())``

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
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

3 REPLIES 3

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!

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

Frequent Visitor

Thanks!

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors