Filter Before Date dynamically in Power Query

[geofspa]

Hi

I am really new to Power Query - please be gentle

I have been set a task to "automate" invoice target monitoring that can be updated, maybe weekly, that shows account controllers how close to achieving their monthly targets they are. The problem I am facing is the financial targets shown should only be for invoices due before the end of "This Month" but the data I am working with includes dates beyond this. Simply put is how can I dynamically filter in power query to only include everything before the beginning of next month.

Currently I have coded a hard filter for the begining of next month

= Table.SelectRows(TargetTotal, each [Due Date] < #date(2021, 3, 1))

Could the date here be dynamic?

I have a seperate query that contains calculation for the begining of next month

let
Source = DateTime.FixedLocalNow(),
#"Extracted Date" = Date.From(Source),
#"Converted to Table" = #table(1, {{#"Extracted Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Inserted End of Month" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Column1]), type date),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted End of Month",{{"End of Month", type number}}),
#"Added to Column" = Table.TransformColumns(#"Changed Type1", {{"End of Month", each _ + 1, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Added to Column",{{"End of Month", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"End of Month", "Beginning Next Month"}})
in
#"Renamed Columns"

Could this be used to dynamicaly filter "Before Date"?

Hope this makes sense

Geof

 

[Alex Blakenburg]

There was another PQ question about taking the date from a named cell in a spreadsheet, which I just answered
Does that help you at all ? Its not exactly the same but may either achieve your purpose or give you an idea how you can use your End of Month query as input to your main query.

Filter BEFORE fixed date (Power Query)
I am fairly new to the forum so I am not sure about the protocol is for answering overlapping questions but below is what I responded on the above link.
--------------------------------------------------------------------------------------------------------------------------------
There are quite a few steps to this but they are pretty clear if you read this (its a link):-
Power Query Parameters – How to use Named Cells as Flexible Inputs (by Access Analytic)
It uses your exact same terminology of CutOffDate.

There is a warning about privacy setting but the article was written in May 2018 so I tried it without changing the settings and it seemed to work fine.
I tucked it away some time ago when I was looking at being able to use a cell to determine the data source for the query which is also covered in that same article.
--------------------------------------------------------------------------------------------------------------------------------

 

[geofspa]

Thank you Alex

That has set a whole train of though going - I think I know where I am going with this now

 

[Alex Blakenburg]

Thank you Alex

That has set a whole train of though going - I think I know where I am going with this now

I was hoping it might. ?