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

[Anonymous]

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

[AlexisOlson · Accepted 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

[Anonymous]

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!