Count of status from a range of date

[Lumjing_sangtam]

Hi  I have a below set of data and I have create a Year filter ( Year is derived from "Expiry Date"). I want to get the count When 2020 is selected in the filter it should count from Max date to the Min date the count of status  Red, Green and Yellow and same with Year 2021, 2022 and 2023. Can you please help.

Expiry Date	Status 2020	Status 2021	Status 2022	Status 2023
3/31/2022	Red	Red	Red	Green
3/31/2022	Red	Red	Red	Green
3/31/2022	Red	Red	Red	Green
10/10/2023	Red	Red	Yellow	Green
7/9/2024	Yellow	Red	Yellow	Green
7/9/2024	Yellow	Red	Yellow	Red
7/8/2014	Yellow	Red	Yellow	Red
1/1/2021
3/29/2021
3/31/2021
3/31/2022	Red	Yellow	Red	Red
10/10/2023	Red	Yellow	Red	Red
7/9/2024	Red	Yellow	Red	Red
7/8/2014	Red	Yellow	Red	Red
1/14/2020	Red	Yellow	Red	Red
1/14/2020	Red	Yellow	Red	Red
10/10/2023	Red	Red	Green	Red
10/10/2023	Red	Red	Green	Red
7/14/2026	Red	Red	Green	Red
7/14/2026	Red	Red	Red	Red
10/10/2023	Red	Red	Red	Red
3/31/2022	Red	Green	Red	Yellow
3/31/2022	Red	Green	Red	Yellow
3/31/2022	Red	Green	Red	Yellow
3/31/2022	Red	Green	Red	Yellow
10/10/2023	Red	Green	Red	Yellow
10/10/2023	Red	Red	Red	Yellow
3/31/2022	Red	Red	Red	Yellow
10/10/2023	Green	Red	Red	Red
3/31/2022	Green	Red	Red	Red
3/31/2022	Green	Red	Red	Red

[Chanty4u]

Try expression like:

count({<DateField={'$(=Date(max(DateField)))' , '$(=Date(min(DateField)))'}>}Status)

[nevilledhamsiri]

Please refer attachment. Straight table will help to sort from max to min date.

[Lumjing_sangtam]

@Chanty4u , thanks for the input. I used the below code but the "Expiry date" is not getting selected when I select 2021 in the year filters please see below SS. I want to select "Expiry date" from Max date to Min date when selecting on the year filter. can you please help how to achieve this 

=count({<[Expiry Date]={'$(=[Max Date])' , '$(=[Min Date])'},[Status 2021]={'Red'}>}[Product code])

[Lumjing_sangtam]

I want to be able to be able to use them in filter like when I select 2021. it should give me a count of Red status from max date to min date. can you help to achive this