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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
tharris
Frequent Visitor

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

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!

1 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())))

 

 

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

Use DateTime.Date(DateTime.LocalNow())

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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())))

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors