Forum Discussion
Query Editor date filtering
I am trying to filter a column based on date and I only care about the records that are equal to or less than two years old
Hi smithmc3,
Please refer to the steps below.
- Select any date when filter data and click OK button.
- Change the date #datetime(2017, 3, 16, 0, 0, 0) to Date.AddDays(DateTime.LocalNow(),-730))
Regards,
Charlie Liao
7 Replies
Hi smithmc3,
Please refer to the steps below.
- Select any date when filter data and click OK button.
- Change the date #datetime(2017, 3, 16, 0, 0, 0) to Date.AddDays(DateTime.LocalNow(),-730))
Regards,
Charlie Liao
So I made the change {= Table.SelectRows(#"Changed Type", each [Transcript Completion Date] <= Date.AddDays(DateTime.LocalNow(), -730))}
Then I noticed it was only showing 52 rows instead of 999+. Looked at some of the dates and it was only showing those more than two years old ... changed "<=" to ">=" and we where back in business. Thanks for the help!
v-caliao-msft Followed this post and implemented solution but getting error "we cannot apply < operator to types date and datetimezone". Which type do I need to use?
First make filter, choose any date
Next open query editor and find code for filter
Next I replace with your code snippet
I get this error
For all the people having problems with "we cannot apply < operator to types date and datetimezone", you are comparing Dates and DateTimes. As the original answer gives a DateTime, you must convert it to Date.
This can be accomplished adding a Date.From function before the DateTime.LocalNow() one. The original answer wolud be transformed to:
"> Date.AddDays(Date.From(DateTime.LocalNow()), -730))"
