Count measure for fiscal years

DAX DAX Calculations
1

Hi there,

I have two tables: dates and Authorisation. Authorisation table contains information about individuals holding practice certificates which also includes the Authorisation effective date (i.e. Start date of practice certificate) and Authorisation expiry date (i.e. end date of practice certificate ).

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

To be honest, I am not sure how I should transform this logic into measure for all fiscal years. I wrote something like as follow but it did not work.

Count Authorisation FY = CALCULATE(COUNT(‘Authorisation’[AuthorisationID]),FILTER(dates,Dates[FiscalYearOffset] <=0))

I am looking for your help to wirte a measure.

Sample file
Sample_count.pbix (26.4 MB)

2

Hello @leo_89,

Thank You for posting your query onto the Forum.

To achieve the results based on the criteria that you’ve specified where you want the active number of members holding the certificates in that case firstly, you’ll have to make the current relationship in-active i.e. you’ve created an active relationship between the Dates table and Expiry Date column and you got to turn-off that relationship otherwise filterations will happen based on that.

Once that relationship is turned-off, below is the measure alongwith the screenshot of the final results provided for the reference -

Active Practice Certificates = 
CALCULATE( COUNTROWS( Authorisation ) , 
    FILTER( VALUES( Authorisation[AuthorisationEffectiveDate] ) , 
        Authorisation[AuthorisationEffectiveDate] <= MAX( Dates[Date] ) ) , 
    FILTER( VALUES( Authorisation[AuthorisationExpiryDate] ) , 
        OR(
            Authorisation[AuthorisationExpiryDate] >= MIN( Dates[Date] ) , 
            ISBLANK( Authorisation[AuthorisationExpiryDate] ) ) ) )

Final Results
Final Results1392×399 29.6 KB

I’m also attaching the working of the PBIX file as well as links of the articles or videos based on this topic for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample_count - Harsh.pbix (26.4 MB)

1 Like
3

Hello @leo_89, just following up if the response from @Harsh help you solve your inquiry?

We’ve noticed that no response has been received from you since November 3. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hi @leo_89, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.