Filtering records in Query Editor for Groups

[kkanda]

I have a table in Query Editor in the following format:

Equipment	MeasureDate
A	12/12/2021
A	10/21/2021
B	10/7/2020
B	12/3/2019
B	1/5/2019
C	11/23/2019
C	2/11/2019
D	1/2/2020
D	3/21/2019

This  table in Query Editor has the Equipment  grouped and Measure Dates  in descending order.

The requirement is if the latest Measure Date for any Equipment is in the year 2019 or earlier, the entire set of records for that Equipment should be filtered. This will filter the records for Equipment C but not for B or D, where the latest Measure Date is after year 2019, even though some Measure Dates are in 2019. I will appreciate if any of the experts suggest a solution.

Thank you

Krishna

[kkanda]

Thank you @HotChilli ... this solution worked for me.

[HotChilli · Accepted solution]

Perform a Group By on Equipment with 2 aggregations

Max on measure date     

and [all rows]   (use the Advanced option to see these).

That should give 3 columns.  Use the filter from the column dropdown on the Max Date column.  I think you want 'date > last day of 2019'.

That will leave the rows you want.  Expand the All Rows to return the fields you need.