Powerquery Keep only next N months of data

[blytonpereira]

Hello

I have a column named Date like below. The EARLIEST DATE is dyanmic, that is sometimes it can start with any month.

Date (MM/DD/YYYY)

09/01/2018

09/01/2018

09/01/2018

10/01/2018

11/01/2018

12/01/2018

01/01/2019

02/01/2019

I would like to keep only the next 6 Months of dates and then i will filter out all other rows. This formulas needs to be dynamic so the NEXT N months where N=6

I am thinking it can be done with an IF statement but not sure how to write it properly so would need some help here.

Another option I thought could be to Group by dates and filter only the next N months, but this method is extremely slow and mostly hangs because the dataset is large. The file can be found here

https://drive.google.com/open?id=1gyPL3-BALBqMWX1Mu4KasypS46nk7zUV

The query is named "STAT_APR"

Myabe the IF statement will work better?

Thanks

[v-chuncz-msft · Accepted solution]

@blytonpereira,

You may add step below.

= Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Date]) in each [Date] < Date.AddMonths(Date.StartOfMonth(earliest), 6))

[Ashish_Mathur]

Hi,

Instead of the last Group by step, see if this line speeds up the process

#"Added Custom" = Table.AddColumn(#"Removed Columns", "First date", each List.Min(#"Removed Columns"[Date]))

This will give you the minimum date from the Date column.  You may now take a difference of months between the Date and this new column and filter out all those >6.

If this is also slow then i guess the best way would be to load all the date and in the DAX formula, we try to exclude all dates beyond 6 months of the first date.