filter last n months in query editor

[Anonymous]

I would like to limit my dataset to return only last two months availabe. 

As my last month in a dataset is not the current month. For instance:

-current month is March 2019

-the latest month in the dataset is December 2018

So I would like to filter Dec 2018 and Nov 2018...

But I would like it to be dynamic. I mean, whenever my data grows and a new month be availabe, I would like to have always last 2 month from the latest one in the dataste.

Hope it is clear.

My date column looks like this:

and at this moment contains dates from Dec 2016 to Dec 2018

Thanks in advance!

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

Please try this query below. In my test file, I have the date from 2018/1/1 to 2018/12/31.

=Table.SelectRows(#"Changed Type", each [Date] > Date.AddMonths(List.Max(#"Changed Type"[Date]),-2))

Here is the output.

More details, please refer to my attachement.

Reference, you also could have a look at this similar thread.

Best  Regards,

Cherry

[Anonymous]

I followed this formula and the resulting filter shows the last day of -3 month?

For example, the last date on my date column is 7/31/2021 and using the M function above, I should only show filters between 6/01/2021-7/31/2021 but I show 5/31/2021 - 7/31/2021. I think the function only manipulates the date and does not take in account the # of days in each month.

This is what I used to filter to true last 2 months:
"= Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNMonths([Date], 2))"

[Anonymous]

The M is working, thanks Cherry!