Reply
Super User
Posts: 1,183
Registered: ‎12-29-2015
Accepted Solution

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.


Accepted Solutions
Super User
Posts: 1,468
Registered: ‎11-25-2016

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

Highlighted
Super User
Posts: 1,468
Registered: ‎11-25-2016

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


All Replies
Super User
Posts: 1,468
Registered: ‎11-25-2016

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)
Super User
Posts: 1,620
Registered: ‎09-06-2015

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.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Posts: 1,468
Registered: ‎11-25-2016

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)
Super User
Posts: 1,468
Registered: ‎11-25-2016

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)
Highlighted
Super User
Posts: 1,468
Registered: ‎11-25-2016

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)
Super User
Posts: 1,183
Registered: ‎12-29-2015

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!

Frequent Visitor
Posts: 14
Registered: ‎07-10-2018

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

[ Edited ]

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!