Convert Date/Time in UTC to Local Time with Daylight savings

Thanks for sharing this solution, it's pretty disappointing that PowerQuery doesn't have proper time zone support.

I've tweaked it for conversion from UTC to UK time (aka Europe/London in tzdata)

From When do the clocks change? - GOV.UK (www.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.

The period when the clocks are 1 hour ahead is called British Summer Time (BST). There’s more daylight in the evenings and less in the mornings (sometimes called Daylight Saving Time).

When the clocks go back, the UK is on Greenwich Mean Time (GMT).

---

datetime version:

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// From https://www.gov.uk/when-do-the-clocks-change
// 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.

// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),

isSummerTime =
		(date = ForwardDate and time >= #time(1,0,0))
	    or
		(date > ForwardDate and date < BackDate) 
		or 
		(date = BackDate and time < #time(1,0,0)),


timeZone = Number.From(isSummerTime),

Europe_London = datetimecolumn + #duration(0, timeZone, 0, 0)

in
    Europe_London

datetimezone version:

let
    Source = (datetimecolumn as datetimezone) =>

let

// This version ignores the zone information in the input, but adds it to the output

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),

// From https://www.gov.uk/when-do-the-clocks-change
// 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.

// Last Sunday in March
ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
// Last Sunday in October
BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),

isSummerTime =
		(date = ForwardDate and time >= #time(1,0,0))
	    or
		(date > ForwardDate and date < BackDate) 
		or 
		(date = BackDate and time < #time(1,0,0)),

timeZone = Number.From(isSummerTime),

Europe_London = DateTime.AddZone(DateTimeZone.RemoveZone(datetimecolumn) + #duration(0, timeZone, 0, 0) , timeZone)

in
    Europe_London
in
    Source

I hope that's useful to someone!

Hi bogdans,

I will test this for you and inform you as soon as I get it . And you also could refer to this link for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, great solution!  However, I was getting "We couldn't authenticate with the credentails provided.  Please try again."  error after I refreshed the query.  My datasource is a SharePoint List and it showed that I was signed in but the credentails couldn't be autenticated.  The error went away after I removed the invoked function columns.  Any idea how to fix this?

Hey! I created this DAX formula to convert from UTC to PDT. Considering the following rules:

Daylight Saving: Pacific Daylight Time (PDT) is a daylight saving/summer timezone, however during winter some places switch clocks for one hour back and observe Pacific Standard Time (PST).

Start: Pacific Daylight Time (PDT) started on Sunday, March 14, 2021 at 2:00 am local time and clocks were set one hour forward to Sunday, March 14, 2021, 3:00 am. Daylight saving starts annually the on second Sunday of March

End: Pacific Daylight Time (PDT) ends on Sunday, November 7, 2021 at 2:00 am local time and clocks are set one hour back to Sunday, November 7, 2021, 1:00 am local standard time instead. Daylight saving ends annually the on first Sunday of November

UTC to PDT = 
VAR CurrentDate = DATE(YEAR('Date'[Date]),MONTH('Date'[Date]),DAY('Date'[Date]))
var CurrentTime = TIME(HOUR('Date'[Date]),MINUTE('Date'[Date]),SECOND('Date'[Date]))
var March = DATE(2022,3,1)
var November = DATE(2022,11,1)
VAR SecondSundayMarch = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(March) && 
        DAY([Date]) > 7 && 
        DAY([Date]) < 15 && 
        WEEKDAY([Date],1) = 1)
VAR FirstSundayNov = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(November) && 
        DAY([Date]) >= 1 && 
        DAY([Date]) < 8 && 
        WEEKDAY([Date],1) = 1)
VAR IsSummerTime = OR(AND(CurrentDate = SecondSundayMarch, CurrentTime >= time(9,0,0)),  OR(AND(CurrentDate > SecondSundayMarch, CurrentDate < FirstSundayNov), AND(CurrentDate = FirstSundayNov, CurrentTime <= time(8,59,0))))
VAR TimeDiff = TIME(8-IsSummerTime,0,0) // If is summer time diff = 7 else time diff = 8
RETURN CurrentDate-TimeDiff

I could't find a simple Dax for this so I came up with a pretty simple solution. This is UTS to Central with DLS.

My problem is fixed by making sure you are on correct time & time zone.