Dynamic DAX for UTC to EDT (Daylight time) conversion.

[Libin7025]

Dynamic EDT&EST = VAR OriginalDateTime = src_system_info[loaded_at]

VAR CurrentYear = YEAR(TODAY())

// Calculate the second Sunday of March for the current year

VAR MarchFirst = DATE(CurrentYear, 3, 1)

VAR Weekdayofmarchfirst = WEEKDAY(MarchFirst)

VAR MarchSecondSunday =

SWITCH(

    true(),

    Weekdayofmarchfirst = 1, MarchFirst + (7 - Weekdayofmarchfirst) + 1,

    Weekdayofmarchfirst > 1, MarchFirst + (14 - Weekdayofmarchfirst) + 1

)

//Calculate the first sunday of November for current year

VAR NovemberFirst = Date(CurrentYear, 11, 1)

VAR NovemberFirstSunday = NovemberFirst + (7 - WEEKDAY(NovemberFirst)) + 1  

VAR EDTStart = MarchSecondSunday // Second Sunday of March

VAR ESTStart = NovemberFirstSunday // First Sunday of November

VAR AdjustedDateTime =

    SWITCH(

        TRUE(),

        OriginalDateTime >= EDTStart && OriginalDateTime < ESTStart,

        OriginalDateTime - TIME(4, 0, 0),  // Eastern Daylight Time (EDT) UTC offset is -4 hours

        OriginalDateTime >= ESTStart && OriginalDateTime < EDTStart + 365, // Transition occurs within a year

        OriginalDateTime - TIME(5, 0, 0),  // Eastern Standard Time (EST) UTC offset is -5 hours

        TRUE(),

        BLANK()

    )

RETURN

    FORMAT(AdjustedDateTime, "DD MMM YYYY") & " at " & FORMAT(AdjustedDateTime, "hh:mm:ss AM/PM") & IF(HOUR(AdjustedDateTime) = 0, " ET", " EDT")

[Greg_Deckler · Accepted solution]

@Libin7025 Is this a question or a post on a helpful DAX calculation? If the latter, you might consider posting it to the Quick Measures Gallery. Quick Measures Gallery - Microsoft Fabric Community

[Libin7025]

It's a helpful DAX calculation. Thanks for directing me to the right path to post this.