#"Andre kolonner fjernet" = Table.SelectColumns(eplehuset_order_events, ), Could you give me a quick pointer on how to integrate this into the query? The datetimecolumn is called "created". This is really great! I'm just not quite sure how to implement this in Power BI. TimeZone = 1 + Number.From(isSummerTime),įor summer time dates from March last Sunday at 2:00am until October last Sunday at 2:59:59am you will get your datetime +2 hours and for winter time dates from October last Sunday 3:00am until March last Sunday at 1:59:59am you will get your datetime +1 hour. (date = lastSundayOfOctober and time < #time(2,0,0)), (date > lastSundayOfMarch and date < lastSundayOfOctober) IsSummerTime = (date = lastSundayOfMarch and time >= #time(2,0,0))
LastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday), LastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday), RETURN think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:įor replenishment and minor correction '>=' to ' VAR TimeDiff = TIME(8-IsSummerTime,0,0) // If is summer time diff = 7 else time diff = 8 YEAR('Date') = YEAR(CurrentDate) &ĭAY() = time(9,0,0)), OR(AND(CurrentDate > SecondSundayMarch, CurrentDate < FirstSundayNov), AND(CurrentDate = FirstSundayNov, CurrentTime <= time(8,59,0)))) Var CurrentTime = TIME(HOUR('Date'),MINUTE('Date'),SECOND('Date')) VAR CurrentDate = DATE(YEAR('Date'),MONTH('Date'),DAY('Date')) Daylight saving ends annually the on first Sunday of November (date = BackDate and time < #time(1,0,0)),Įurope_London = DateTime.AddZone(DateTimeZone.RemoveZone(datetimecolumn) + #duration(0, timeZone, 0, 0), timeZone)Įnd: Pacific Daylight Time (PDT) ends on Sunday, Novemat 2:00 am local time and clocks are set one hour back to Sunday, November 7, 2021, 1:00 am local standard time instead. This version ignores the zone information in the input, but adds it to the output (date = ForwardDate and time >= #time(1,0,0)) and back 1 hour at 2am on the last Sunday in October.įorwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),īackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday), In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, When the clocks go back, the UK is on Greenwich Mean Time (GMT). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time). The period when the clocks are 1 hour ahead is called British Summer Time (BST). I've tweaked it for conversion from UTC to UK time (aka Europe/London in tzdata)įrom When do the clocks change? - GOV.UK (In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October.
Thanks for sharing this solution, it's pretty disappointing that PowerQuery doesn't have proper time zone support. If you have any question or if you find any error on the code, just let me know.
So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hoursĪccording to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) TimeZone = (7 - Number.From(isSummerTime))*-1, (date > SecondSundayOfMarch and date = #time(1,0,0)), IsSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0)) SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday), Think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:įirstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
0 kommentar(er)
