cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcelBeug Super Contributor
Super Contributor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

You're welcome @DavidMoss

 

As a matter of fact I just copied the DateTimeZone.FixedUtcNow() function from the example, which - in my opinion - doesn't mean that time zones are taken into account.

 

Actually I spent some time on the subject of converting international times and I even created a function to convert a date/time from one Windows time zone to the date/time in any other Windows time zone in the period 2000 through 2027.

As a basis, I created about 130 files (1 for each Windows time zone) with all date/times on which the clock was adjusted in that Windows time zone, typically DST (Daylight Saving Time) switches, with UTC-date/time and corresponding local date/time directly after the switch. During creation I had to adjust the Windows time zone on my computer about 130 times...

This would be closest to a reliable conversion, but then again it should be noted that any historic date/time conversions (e.g. before 2007), become very unreliable as not all historic information is available in Windows, and any future date/times may be subject to government decisions.

Microsoft tries to keep up the pace with developments, https://blogs.technet.microsoft.com/dst2007/ but sometimes decisions are taken on short notice and still not everything is accounted for, like temporary DST suspensions in some Islamitic countries during Ramadan.

 

As a last remark: a specific point of attention with DST switches are missing times (when clocks are moved forward) and ambiguous times (when clocks are moved back).

 

Maybe too much information but I understood this would be an informative topic. Smiley LOL

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

@descalabro Power Query has several Date functions to add/subtract periods from dates (can also be used with Date/Times and Date/Time/Zones):

Date.AddDays

Date.AddMonths

Date.AddQuarters

Date.AddWeeks

Date.AddYears

 

Remark: if the result would be a non existent date (e.g 1 month added to January 31), then'the result will be the last day of the month.

Specializing in Power Query Formula Language (M)
DavidMoss Member
Member

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Further Power Query M language or as some call it PQL documentation here

https://msdn.microsoft.com/en-us/library/mt253322.aspx

 

Super User III
Super User III

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Hi @DavidMoss, thx for the trust, but you've probably recognized by now, that @MarcelBeug is at least on par with me by now 😉

 

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

Imke Feldmann

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




descalabro Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

@MarcelBeug, thanks a lot. 🙂

 

I'm looking for a way to reduce PBI file size. My PBI file is connected to 8 Access files which I am not able to alter in any way. Is there a way to use this kind of filter right on the loading step, so that only a part of each table is loaded into PBI?

 

Or perhaps, is there a way to actually delete rows based on the same method as this filter?

 

I know the filter prevents data from being calculated on the actual report, but still I would like to save time when opening, refreshing and also publishing the file.

 

Once again, thank you,

 

 

TJ

Danilo_Chavez Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

I love you DavidMoss and MarcelBeug, this is awesome! 

You have no idea how much i needed this. Thanks guys!

marcelammelo Regular Visitor
Regular Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

Awesome! Thanks for sharing, buddy!

 

aaande8 Frequent Visitor
Frequent Visitor

Re: Power Query M filter data by Dynamic Date (PQ not DAX) Start of Month

What would be the formula to filter data based on data prior to current month?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors