cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

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? Please mark it as a solution.

Proud to be a Datanaut!
2 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Community Champion
Community Champion

Re: Query Editor: Find the date of a particular Sunday

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

MarcelBeug Community Champion
Community Champion

Re: Query Editor: Find the date of a particular Sunday

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

Re: Query Editor: Find the date of a particular Sunday

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

Super User III
Super User III

Re: Query Editor: Find the date of a particular Sunday

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

Re: Query Editor: Find the date of a particular Sunday

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)
MarcelBeug Community Champion
Community Champion

Re: Query Editor: Find the date of a particular Sunday

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)
MarcelBeug Community Champion
Community Champion

Re: Query Editor: Find the date of a particular Sunday

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

Super User I
Super User I

Re: Query Editor: Find the date of a particular Sunday

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? Please mark it as a solution.

Proud to be a Datanaut!
Highlighted
kdiggitydawg
Frequent Visitor

Re: Query Editor: Find the date of a particular Sunday

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!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors