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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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