Can someone help me convert this power query function to DAX? (Changing UTC to NZT)

[Anonymous]

Hi,

I need to change UTC to NZT in Dax. I can't do it in PowerQuery because I'm using DirectQuery rather than import and it won't work unless I import my tables which I don't want to do.

Essentially I need to convert timestamps from GMT to UTC and found this. Can someone help me convert it to DAX?

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfApril = Date.StartOfWeek(#date(Date.Year(date), 4, 7), Day.Sunday),
LastSundayOfSeptember = Date.StartOfWeek(#date(Date.Year(date), 9, 30), Day.Sunday),

isSummerTime =	(date = LastSundayOfSeptember and time >= #time(1,0,0))
	        or
		(date > LastSundayOfSeptember and date < firstSundayOfApril) 
		or 
		(date = firstSundayOfApril and time >= #time(1,0,0)),


timeZone = (12 + Number.From(isSummerTime))*1,

NZT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    NZT

[Anonymous]

Thank you!

[Vera_33 · Accepted solution]

Hi @Anonymous 

You are adding a DAX calculated column like this?

NZT_dt = 
VAR CurY =
    YEAR ( yourTable[OriginDT] )
VAR StartST =
    DATE ( CurY, 4, 7 ) - WEEKDAY ( DATE ( CurY, 4, 7 ), 1 ) + 1
VAR EndST =
    DATE ( CurY, 9, 30 ) - WEEKDAY ( DATE ( CurY, 9, 30 ), 1 ) + 1
RETURN
    IF (
        yourTable[OriginDT] > StartST
            && yourTable[OriginDT] < EndST,
        yourTable[OriginDT] + ( 13 / 24 ),
        yourTable[OriginDT] + ( 12 / 24 )
    )

?