Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KHorseman
Community Champion
Community Champion

Query Editor: Find the date of a particular Sunday

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




2 ACCEPTED SOLUTIONS
MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

View solution in original post

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

 

 

Links:

Power BI Desktop file

Excel file

 

Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

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:

 

Convert UTC to local - sneak preview.png

Specializing in Power Query Formula Language (M)

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

 

 

Links:

Power BI Desktop file

Excel file

 

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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!

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.