Select Distinct Cars By Max Inpsection Date

Question

Need to count the number of distinct Cars by Max Inspection Date and report on their Inspection Due Date.  On my Metric sheet I have a below metric defined but realized the counts were off because the Cars being counted needed to be distinct :

Current Formula:=COUNTIFS({STATS}, "In Service", {Due Date}, MONTH(@cell) = $[Month#]

Data Sheet

Based on the Data Sheet, the total cars Due for Inspection in July is 1 (Car 100).   Car 101 need to be counted in August based (Max(Due Date).

I tried INDEX(COLLECT...but getting lost because I think I need to first collect distinct cars based on max date and then compare to the Month@cell.

Thanks in advance,

Tnorman · Answer

Another option would be to collect the distinct cars and add a helper column Active/Inactive to switch off the earlier inspection???

Tnorman · Answer

The max inspection date overrides the earlier date. Normally inspections occur every 90 days, sometimes cars are spot checked which will generate a new inspection renewal date.

Paul Newcome · Answer

I'm not sure I understand why car 101 is being pushed to August when it has the same due date as car 100. Or are you trying to count how many unique days there are in July that have due dates on them?