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

Libin7025 — Wed, 04 Oct 2023 11:56:13 GMT

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

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

Greg_Deckler — Wed, 04 Oct 2023 12:29:00 GMT

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 — Wed, 04 Oct 2023 13:45:36 GMT

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