Query Editor: Find the date of a particular Sunday

[KHorseman]

I need a method of checking whether or not an arbitrary date fell within daylight saving time. This has to be done in the query editor. Basically, all of our datetime values are UTC, but we need to convert them retroactively to local times. The DateTimeZone localization functions are no help here for reasons I won't get into. These are historic datetime stamps, so they need to be offset appropriately for the time at which they happened. Simply offsetting by -5 hours won't work for half the year, and -4 won't work for the other half.

So here's the plan:

1. For each date, find the date of the second Sunday in March of that date's year. If the date is earlier than this Sunday, offset by -5 hours.
2. If on or after that Sunday, find the date of the first Sunday in October of the year. If the date is earlier than this Sunday, offset by -4 hours.
3. If not, offset by -5 hours.

I'm stuck on the "find the second/first Sunday" parts of this. Query guru @ImkeF do you have any ideas? I'm thinking maybe I should write a couple of custom functions outside my main query, then call them in a custom column formula, but I just can't even figure out how to use the date formulas to find the Sundays in question in the first place.

[MarcelBeug · Accepted solution]

Alternatively you can use a table with clock switches in your time zone and a function that will convert date/times, based on that table, as illustrated in this video (required table and function DateTimeBetweenFixedAndDSTZone are included in the comment below the video).

[MarcelBeug · Accepted solution]

Alternatively you can use a table with clock switches in your time zone and a function that will convert date/times, based on that table, as illustrated in this video (required table and function DateTimeBetweenFixedAndDSTZone are included in the comment below the video).

[ImkeF]

Hi @KHorseman.

Marcels solution are normally very good, so please shout if it doesn't work and I will show how to do like you suggested.

[MarcelBeug]

Thanks Imke.

I can create an example for this particular case if I'd know which Windows time zone is involved.

I can't find any time zone that matches the specs (-5 to -4 on the 2nd Sunday in March; -4 to -5 on the first Sunday in October).

[MarcelBeug]

Later today I will present a solution with a Power Query function to convert UTC date/times to local date/times, based on a pattern of DST clock switches you can define in the function parameters.

You may expect a video with links to a Power BI Desktop file and an Excel workbook with this function and examples.

For now, I won't be able to work on this for a couple of hours, but I can already present a sneak preview:

[MarcelBeug · Accepted solution]

Please find my solution in this video and in the links below,

Links:

Power BI Desktop file

Excel file

[TrentoTrent]

Hey Necro-ing this thread since it shows up on google.

The file below can no longer be downloaded 😞 But I put together a quick hack that can do this (mainly to calculate to a specific timezone).  This can maybe help future users.

Not as pretty as Marcel's solution (Who made it a nice function) but it worked enough for me. I did not need to solve this generically. 

Others may improve on this as necessary.

let
    CurrentUTCTime = DateTimeZone.UtcNow(),
    DayOfWeekStartUTC = Day.Sunday,
    DayOfWeekEndUTC = Day.Sunday,
    MonthStart = 3,
    MonthEnd = 11,
    WeekStart = 2, 
    WeekEnd = 2,
    TimeStartUTC = 8,
    TimeEndUTC = 9,
    FirstOffset = -6,
    SecondOffset = -7,

    CurrentYear = Date.Year(CurrentUTCTime),

    // Start Date Logic:
    StartDate = Date.AddWeeks(#datetimezone(CurrentYear, MonthStart, 1, TimeStartUTC, 0, 0, 0, 0) , WeekStart - 1),
    StartListOfWeek = List.DateTimeZones(StartDate, 7, #duration(1,0,0,0)),
    StartDatetime = List.Select(StartListOfWeek, each Date.DayOfWeek(_) = DayOfWeekStartUTC){0},

    // End Date Logic
    EndDate = Date.AddWeeks(#datetimezone(CurrentYear, MonthEnd, 1, TimeEndUTC, 0, 0, 0, 0) , WeekEnd - 1),
    EndListOfWeek = List.DateTimeZones(EndDate, 7, #duration(1,0,0,0)),
    EndDatetime = List.Select(EndListOfWeek, each Date.DayOfWeek(_) = DayOfWeekEndUTC){0},

    // Offset Calculation:
    Offset = if CurrentUTCTime > StartDatetime and CurrentUTCTime < EndDatetime then FirstOffset else SecondOffset
in
    DateTimeZone.SwitchZone(CurrentUTCTime, Offset)

[Anonymous]

pretty impressive @MarcelBeug !

Apologies if it's obvious to everyone else:

1 - after this function and table/query is created, how exactly is it used in the report?  Is there additional DAX needed to run a comparison to show the proper time?

2 - does it matter if the months skip sometimes when arbitrarily setting 30 days apart?

thank you!

[KHorseman]

Thanks @MarcelBeug. In the end I just made a small table with the start and end dates of daylight savings from 2013-2025 and used that as a conversion key, but I have another dataset that I'll be working on this week that I think will be better suited to use your other solution. Fantastic work!