Need help to create aggregation formula

[#1]

1.I want a user to enter a date and store in a variable.

2.Then I want all the rows having date less than the entered date

3.Then for each ID I want to find max date and if max date is having status as "Open" then count the ID other wise don't count

I am attaching a excel file having three columns.

Basically I want to know as of entered date at ID level max date count all IDs if status is open for their max date

[Kushal_Chawda]

Create Straight Table

Dimension:

ID

Expression:

=count ({<Date ={"$(=max(Date)"}, Status ={"Open"}>}ID)