Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Proud to be a Super User!
Solved! Go to 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).
Please find my solution in this video and in the links below,
Links:
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).
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
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).
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:
Please find my solution in this video and in the links below,
Links:
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!
Proud to be a Super User!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |