Advanced Editor - Date Filter to include last 2 months, today, and all future or next 5 years

[tharris]

Hello Community,

I have been trying to filter my dataset as the parameters above.

I can set it up to give me the last 2 months plus the next 5 years, but that excludes today.

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns3", each Date.IsInPreviousNMonths([Date], 2) or Date.IsInNextNYears([Date], 5)),

I can also set it up where it will give me the last 2 months, and all future from a specific date, which I can selet today, but the problem here is as the future comes, this will be completely wrong since it is a hard coded date.

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNMonths([Date], 2) or [Date] >= #date(2020, 4, 17)),

Lastly, I have also tried to combine both by doing a manual edit in Advanced editor as below, but this does not work as Date is in Previous and Date is in the Next only work in Basic filtering, and that doesn't allow me to add today.

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNMonths([Date], 2) or Date.IsInCurrentDay([Date]) or Date.IsInNextNYears([Date],5)),

What I am thinking is that I need to use option 2, but replace the hard coded date with a function, but haven't been able to find a function that will work here.

Please help!

Thanks!

[edhans]

Use DateTime.Date(DateTime.LocalNow())

That is the Power Query equivalent to TODAY() in Excel.

[tharris · Accepted solution]

Yes, I am aware of that @edhans . I am working on modeling.

I figured out a solution that works.

= Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNDays([Date], 60) or [Date] >= Date.From(Date.StartOfMonth(DateTime.LocalNow())))

[edhans]

Sorry I misunderstood your request. I thought this was your question.

"What I am thinking is that I need to use option 2, but replace the hard coded date with a function, but haven't been able to find a function that will work here."

I was replacing the hardcoded date with a function.