Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
AlexisOlson
Super User
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

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.