I have a column named Date like below. The EARLIEST DATE is dyanmic, that is sometimes it can start with any month.
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
The query is named "STAT_APR"
Myabe the IF statement will work better?
Go to Solution.
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))
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.
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.