Forum Discussion

Icon for Advocate I rank

Advocate I

9 years ago

Solved

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

v-caliao-msft
9 years ago
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

Icon for Microsoft Employee rank

Microsoft Employee

9 years ago

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

smithmc3
Advocate I
9 years ago
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!
hxkresl
Advocate III
9 years ago
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
  Advocate I
  9 years ago
  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
    Advocate III
    9 years ago
    smithmc3
    I tried Date/Time. Error states 'We cannot apply operator < to types DateTimeZone and DateTime'
    
    any other suggestion highly appreciated.

Related content