So I'm working on a report that records time employees clock in and clock out of various distributed around the world. The time data is recorded in UTC time and I had to write a code to convert the recorded time to the actual time zones of each location using various IF statements; it was tedious but it eventually worked then it hit me that I had to account for day light savings time which I believe to be stupid and should be done away with.
Anyways, I need to write a dax that selects the 2nd Sunday of March and First Sunday of November every year at 2 am so I don't have to go in to change the date every year as day light savings doesn't happen on the same day every year. I most definitle appreiciate the help! I have attached my current dax code which works but only for this year. It is long and tedious so sorry in advanced
SiteTimeZone(DST) = IF('Access Personnel'[Site] = "Cheyenne",'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[Site] = "Amsterdam",'Access Personnel'[AreaAccessTime]+TIME(1,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,26),'Access Personnel'[AreaAccessTime]+TIME(2,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,29),'Access Personnel'[AreaAccessTime]+TIME(1,0,0),IF('Access Personnel'[Site] = "El Dorado",'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(5,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[Site] = "Navajo",'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[Site] = "Petrolia",'Access Personnel'[AreaAccessTime]-TIME(5,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(4,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(5,0,0),IF('Access Personnel'[Site] = "Puget Sound",'Access Personnel'[AreaAccessTime]-TIME(8,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(8,0,0),IF('Access Personnel'[Site] = "Tulsa",'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(5,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[Site] = "Woods Cross",'Access Personnel'[AreaAccessTime]-TIME(7,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,03,12),'Access Personnel'[AreaAccessTime]-TIME(6,0,0),IF('Access Personnel'[AreaAccessDate] >= DATE(2023,11,5),'Access Personnel'[AreaAccessTime]-TIME(7,0,0),'Access Personnel'[AreaAccessTime]))))))))))))))))))))))))
Solved! Go to Solution.
I think you can simplify what you currently have to something much less verbose by separating the pieces:
SiteTimeZone (DST) =
VAR _CityOffset =
SWITCH (
'Access Personnel'[Site],
"Cheyenne", - TIME ( 7, 0, 0 ),
"Amsterdam", TIME ( 1, 0, 0 ),
"El Dorado", - TIME ( 6, 0, 0 ),
"Navajo", - TIME ( 7, 0, 0 ),
"Petrolia", - TIME ( 5, 0, 0 ),
"Puget Sound", - TIME ( 8, 0, 0 ),
"Tulsa", - TIME ( 6, 0, 0 ),
"Woods Cross", - TIME ( 7, 0, 0 )
)
VAR _DST =
IF (
'Access Personnel'[AreaAccessDate] >= DATE ( 2023, 03, 12 )
&& 'Access Personnel'[AreaAccessDate] < DATE ( 2023, 11, 5 ),
TIME ( 1, 0, 0 )
)
RETURN
'Access Personnel'[AreaAccessTime] + _CityOffset + _DST
If you have a calendar table (if you don't, you really should), then you can leverage that to calculate the second Sunday in these months like this:
SiteTimeZone(DST) =
VAR _CityOffset =
SWITCH (
'Access Personnel'[Site],
"Cheyenne", - TIME ( 7, 0, 0 ),
"Amsterdam", TIME ( 1, 0, 0 ),
"El Dorado", - TIME ( 6, 0, 0 ),
"Navajo", - TIME ( 7, 0, 0 ),
"Petrolia", - TIME ( 5, 0, 0 ),
"Puget Sound", - TIME ( 8, 0, 0 ),
"Tulsa", - TIME ( 6, 0, 0 ),
"Woods Cross", - TIME ( 7, 0, 0 )
)
VAR _Year =
YEAR ( 'Access Personnel'[AreaAccessDate] )
VAR _MarchDate =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
Dates,
YEAR ( Dates[Date] ) = _Year
&& MONTH ( Dates[Date] ) = 3
&& WEEKDAY ( Dates[Date] ) = 1
&& DAY ( Dates[Date] ) > 7
)
)
VAR _NovDate =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
Dates,
YEAR ( Dates[Date] ) = _Year
&& MONTH ( Dates[Date] ) = 11
&& WEEKDAY ( Dates[Date] ) = 1
&& DAY ( Dates[Date] ) > 7
)
)
VAR _DST =
IF (
'Access Personnel'[AreaAccessDate] >= _MarchDate
&& 'Access Personnel'[AreaAccessDate] < _NovDate,
TIME ( 1, 0, 0 )
)
RETURN
'Access Personnel'[AreaAccessTime] + _CityOffset + _DST
I think you can simplify what you currently have to something much less verbose by separating the pieces:
SiteTimeZone (DST) =
VAR _CityOffset =
SWITCH (
'Access Personnel'[Site],
"Cheyenne", - TIME ( 7, 0, 0 ),
"Amsterdam", TIME ( 1, 0, 0 ),
"El Dorado", - TIME ( 6, 0, 0 ),
"Navajo", - TIME ( 7, 0, 0 ),
"Petrolia", - TIME ( 5, 0, 0 ),
"Puget Sound", - TIME ( 8, 0, 0 ),
"Tulsa", - TIME ( 6, 0, 0 ),
"Woods Cross", - TIME ( 7, 0, 0 )
)
VAR _DST =
IF (
'Access Personnel'[AreaAccessDate] >= DATE ( 2023, 03, 12 )
&& 'Access Personnel'[AreaAccessDate] < DATE ( 2023, 11, 5 ),
TIME ( 1, 0, 0 )
)
RETURN
'Access Personnel'[AreaAccessTime] + _CityOffset + _DST
If you have a calendar table (if you don't, you really should), then you can leverage that to calculate the second Sunday in these months like this:
SiteTimeZone(DST) =
VAR _CityOffset =
SWITCH (
'Access Personnel'[Site],
"Cheyenne", - TIME ( 7, 0, 0 ),
"Amsterdam", TIME ( 1, 0, 0 ),
"El Dorado", - TIME ( 6, 0, 0 ),
"Navajo", - TIME ( 7, 0, 0 ),
"Petrolia", - TIME ( 5, 0, 0 ),
"Puget Sound", - TIME ( 8, 0, 0 ),
"Tulsa", - TIME ( 6, 0, 0 ),
"Woods Cross", - TIME ( 7, 0, 0 )
)
VAR _Year =
YEAR ( 'Access Personnel'[AreaAccessDate] )
VAR _MarchDate =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
Dates,
YEAR ( Dates[Date] ) = _Year
&& MONTH ( Dates[Date] ) = 3
&& WEEKDAY ( Dates[Date] ) = 1
&& DAY ( Dates[Date] ) > 7
)
)
VAR _NovDate =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
Dates,
YEAR ( Dates[Date] ) = _Year
&& MONTH ( Dates[Date] ) = 11
&& WEEKDAY ( Dates[Date] ) = 1
&& DAY ( Dates[Date] ) > 7
)
)
VAR _DST =
IF (
'Access Personnel'[AreaAccessDate] >= _MarchDate
&& 'Access Personnel'[AreaAccessDate] < _NovDate,
TIME ( 1, 0, 0 )
)
RETURN
'Access Personnel'[AreaAccessTime] + _CityOffset + _DST
Wow thanks!!
It much easier and simpler thank you!!
You just sent me the time and energy of trying to figure it out on my own!