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?
