Power Query dynamic previous months filter

[571202]

Hi all, how can I create a dynamic filter for 'previous months' but excluding last month, ie if it's currently October I want to filter for August and all previous months. Must be dynamic so that in November it filters for September and all previous months.
Is it possible to change the below code to suit?

= Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousMonth([Remedy Date Due]))

Thanks in advance.

 

[Skovgaard]

My first thought - Why not exclude current month in the source / data load?

When I do this, I create a dynamic table with e.g. today(), transform it to a drilldown and load it as only connection.
Then I can use this variable in my source.

/Skovgaard

 

[571202]

My first thought - Why not exclude current month in the source / data load?

When I do this, I create a dynamic table with e.g. today(), transform it to a drilldown and load it as only connection.
Then I can use this variable in my source.

/Skovgaard

Hi @Skovgaard I have lots of other sheets with other filters so I can't delete the data because I need it for them.

I'm brand new to power query so I'm not sure what you mean in the rest of your post sorry.

Can I do what I need with the below filter?

 

[Skovgaard]

OK, I understand why you can't exclude them from the beginning then...

I haven't tried that filter before, so for me being able to help, I need an example of you dataset.
You can either use XL2BB or upload an image.

Or maybe other users can help you with this filter

/Skovgaard

 

[571202]

@Skovgaard I can't upload data but it's just data from May to October and I just want to filter for May through to August, cheers.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic date filter - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[571202]

I have posted on another forum, see link below. I haven't received an answer.

Dynamic date filter - OzGrid Free Excel/VBA Help Forum

 

[Skovgaard]

@Skovgaard I can't upload data but it's just data from May to October and I just want to filter for May through to August, cheers.

Depending on your dataset, below might work...

/Skovgaard

 

[571202]

Unfortunately it gives me September...I only want August and earlier. Can I put a -1 in the formula somewhere?

 

[JGordon11]

= Table.SelectRows(#"Sorted Rows", let dt = Date.AddMonths(Date.From(DateTime.LocalNow()),-1), yr = Date.Year(dt), mo = Date.Month(dt) in each [Remedy Date Due] < #date(yr,mo,1))