cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## DAX code for finding 2nd Sunday of the month of March

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]))))))))))))))))))))))))``

1 ACCEPTED SOLUTION
Super User

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``````

2 REPLIES 2
Super User

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``````

Anonymous
Not applicable

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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors