Distinct count based on max date and other criteria

[rmeng]

Hello,

I need a measure to give me if this site was removed or not. In this case the site is not removed, because in March it´s "N".

But if the Max date is "Y" this should count as 1 removed site.

I have this calculation but it´s wrong.

CALCULATE([Distinct Count HCO MDM ID], FILTER(table,table[SITE_REMOVED_FLG]="Y"))

Can you help me it this?

Thanks in advance

[edhans]

Try this @rmeng 

Site Removed Count = 
CALCULATE(
    DISTINCTCOUNT(Sites[Site ID]),
    Sites[Site Removed] = "Y"
    )

[rmeng]

Sorry for the late reply, the result should be 0 on my table because the result looks at the max date  and if it is Y then the result is 1 if not 0.

The example has the same ID for the 4 rows, you example is correct but you´re comparing different single ID.

Thanks