Query Editor date filtering

[smithmc3]

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

[Ignacio_Vidal]

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

[v-caliao-msft · Accepted solution]

Hi @smithmc3,

Please refer to the steps below.

1. Select any date when filter data and click OK button.
2. Change the date #datetime(2017, 3, 16, 0, 0, 0) to Date.AddDays(DateTime.LocalNow(),-730))

Regards,

Charlie Liao

[hxkresl]

@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

[smithmc3]

@hxkresl mine is set to be just Date/Time not the Date/Time/TimeZone.  If you don't need the timezone portion, I would apply a step prior to your data filter that changes just to Date/Time.

[hxkresl]

@smithmc3

I tried Date/Time. Error states 'We cannot apply operator < to types DateTimeZone and DateTime' 

any other suggestion highly appreciated.

[smithmc3]

@hxkresl can you show the new screenshot of the code? Or could you export it to notepad ++ and attach?

[smithmc3]

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!