Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
)
?
Thank you!
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 )
)
?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |