Forum Discussion
Advocate VPower Query M filter data by Dynamic Date (PQ not DAX) Start of Month
Community ChampionApparently [Start] has DateTime format and the error can be prevented with DateTime.From instead of Date.From:
DateTime.From(Date.StartOfMonth(DateTimeZone.FixedUtcNow()))
Note that the result of DateTimeZone.FixedUtcNow() depends on the timezone on which the code is run, e.g. with me in the Netherlands (CET = UTC +1:00) the beginning of this month (UTC) is 1-1-2017 1:00.
If your data has mixed timezones, then Power Query has no suitable functions to convert those to 1 universal time (UTC).
There are some functions that allow adding/subtracting zone hours / minutes, but nothing dynamic takling into account DST switches and other clock adjustments (e.g. due to government decisions).
As a last remark: note that all functions that are related to system date/time should be interpreted as "... at the date/time of last query refresh".
Advocate VThnaks MarcelBeug for your contribution to this....much appreciated. David
- MarcelBeug
You're welcome DavidMoss
As a matter of fact I just copied the DateTimeZone.FixedUtcNow() function from the example, which - in my opinion - doesn't mean that time zones are taken into account.
Actually I spent some time on the subject of converting international times and I even created a function to convert a date/time from one Windows time zone to the date/time in any other Windows time zone in the period 2000 through 2027.
As a basis, I created about 130 files (1 for each Windows time zone) with all date/times on which the clock was adjusted in that Windows time zone, typically DST (Daylight Saving Time) switches, with UTC-date/time and corresponding local date/time directly after the switch. During creation I had to adjust the Windows time zone on my computer about 130 times...
This would be closest to a reliable conversion, but then again it should be noted that any historic date/time conversions (e.g. before 2007), become very unreliable as not all historic information is available in Windows, and any future date/times may be subject to government decisions.
Microsoft tries to keep up the pace with developments, https://blogs.technet.microsoft.com/dst2007/ but sometimes decisions are taken on short notice and still not everything is accounted for, like temporary DST suspensions in some Islamitic countries during Ramadan.
As a last remark: a specific point of attention with DST switches are missing times (when clocks are moved forward) and ambiguous times (when clocks are moved back).
Maybe too much information but I understood this would be an informative topic. :smileylol: