Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.