topic Dynamic DAX for UTC to EDT (Daylight time) conversion. in Quick Measures Gallery

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

Libin7025 — Wed, 04 Oct 2023 13:51:10 GMT

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

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

Libin7025 — Wed, 04 Oct 2023 13:52:25 GMT

The variable Original Date will have UTC time.

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

Libin — Tue, 23 Apr 2024 10:59:16 GMT

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