How to apply date filter on a date column based on another cutoff date column

[StrPod]

I have two columns (date,cutoff).

scenario: date column has  dates ranging from (2/1/2021 - 2/1/2002) the cutoff is basically current subtract 2 months (date.addMonth(DateTime.LocalNow(), -2). Which ends up listing exactly one date in every row.

Now in PQ editor when I do date filter on the "date" column > I custom filter > advanced  then column = cutoff

operator = is after

value = the date on cutoff.

I get zero rows returned. Instead of getting last two months of data. Can someone tell me what I am doing wrong? I tried to research but the only closest is this article but im not sure how to convert it to my scenario 

Both columns are of date type with no errors 

assume current date is 2/2/2021, just for the purpose of this scenario 

[v-weiyan1-msft · Accepted solution]

Hi @StrPod ,

Based on your description, the operation you did in PQ editor did not return your expected result, which is to return the rows where the Date column is after the Cutoff column date. The reason is that the column you applied the filter to is not the Date column, but the Cutoff column.

I’m not sure if you created the cutoff column for filtering or other purposes.
But if you want to use dynamic filtering to return data from the two months before the current date, you can consider the following method.

Here is the dummy data I created.

The code in the Power Query advanced editor is below.

#"SelectRows" = Table.SelectRows(#"Changed Type", each [Date] > Date.From (Date.AddMonths (DateTime.LocalNow (), -2)))

Result is as below.

For further details，please find attachment.
Or you can consider using Relative Date Slicer.
For more you may refer to:

Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@StrPod , BAsed on what I got.

First filter based on date any date , you will get a step

= Table.SelectRows(#"Added Custom", each [Sales Date] > #date(2024, 2, 17))

then manually edit it like

= Table.SelectRows(#"Added Custom", each [Sales Date] > Date.AddMonths(DateTime.LocalNow(), -2))