Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

filter last n months in query editor

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:

Capture.PNG

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

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

 

More details, please refer to my attachement.

 

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

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

 

More details, please refer to my attachement.

 

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

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Not applicable

The M is working, thanks Cherry!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.