Removing duplicates using measure

Hi Dunner2020 ,

 

I want to create a measure that counts individuals having active practice certificates (i.e. not expired) and displays the measure in a line graph where the x-axis contains the fiscal years (i.e. from FY21 to FY22). So I wanted to show the total no of individuals holding active practice certificates each year.

Authorisation contains information of multiple practicing certificates (i.e. active and expired) of an individual. I want to show the total count of individuals holding active practicing certificates in previous and current fiscal years. For example, for FY17 the logic of picking up holding active practice certificate should be that effective date should be less than or equal to 30-6-2016 and expiry date should be greater than or equal to 1-07-2016

 

---

Based on your description, for FY15, the filter conditions should be:

1. effective date <= 30-6-2014 = MIN ( Dates[Date] ) - 1  => effective date < MIN ( Dates[Date] )
2. expiry date >= 1-07-2014 = MIN ( Dates[Date] )  => expiry date >= MIN ( Dates[Date] )

So, create your measure like so:

Active Practice Certificates - v3 = 
CALCULATE (
    COUNTROWS ( Authorisation ),
    FILTER (
        Authorisation,
        Authorisation[Authorisation_Effective_Date__c] < MIN ( Dates[Date] )
            && Authorisation[Authorisation_Expiry_Date__c] >= MIN ( Dates[Date] )
            && Authorisation[Authorisation_Status__c] = "Authorisations Issued"
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.