Dynamic DAX for UTC to EDT (Daylight time) conversion. 10-04-2023 06:51 AM

[Libin7025]

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

2 hours ago

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

[Libin]

Updated DAX for this use case.


Dynamic EDT&EST Updated = VAR OriginalDateTime = 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 DecemberFirst = DATE(CurrentYear,12,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 >= NovemberFirst && //OriginalDateTime < ESTStart,

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

        MONTH(OriginalDateTime) IN {1, 2, 12} || OriginalDateTime > ESTStart && OriginalDateTime < DecemberFirst || OriginalDateTime > MarchFirst && OriginalDateTime < EDTStart,

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

        BLANK()

    )

RETURN

    FORMAT(AdjustedDateTime, "DD MMM YYYY") & " at " & FORMAT(AdjustedDateTime, "hh:mm:ss AM/PM") & IF(MONTH(OriginalDateTime) IN {1, 2, 12} || OriginalDateTime >= ESTStart && OriginalDateTime < DecemberFirst || OriginalDateTime > MarchFirst && OriginalDateTime < EDTStart, " ET", " EDT")

[Libin7025]

The variable Original Date will have UTC time.