Calculating percentage compliance for annual courses

[helenleggo]

Hello

I have a curated data set with a table named Employee Training, and a Course Completed Date. Some of the courses are required to be completed annually so many staff have completed these courses numerous times. I have a separate table for Course and Frequency which is a lookup table for the course name and frequency.

I have been able to create measures to extract the latest course completion, and one to return only those who have been completed in the last 365 days. 

Where I am having problems is to calculate a percentage compliance for the annual courses. If a person has completed the course more than once it counts each completion into the sum of the courses completed.

I am unable to share the pbix file for organisational reasons. So I am hoping a table will help illustrate the issue. In the table containing 5 people below, the compliance should be 100% as they have all completed the annual course within the past year.

Course template name	Emp ID	Emp First	Emp Last	Course Completed Calendar Date
Code Orange Evac	105958	Mary	Smith	1/12/2022
Code Orange Evac	106892	John	Jamieson	1/12/2022
Code Orange Evac	23231	Una	Brown	1/12/2022
Code Orange Evac	40339	Becca	Jones	1/12/2022
Code Orange Evac	191775	Fred	White	28/11/2022
Code Orange Evac	191775	Fred	White	9/12/2022
Code Orange Evac	191775	Fred	White	7/02/2023

When I use the DAX measure below for courses only completed once the correct result is returned. The same formula used for the above returns 140%

% Comp = DIVIDE([Count Employee Training],'Employee Training Group'[Count Employee Training Group])

Even using Distinctcountnoblank it continues to count all course completions. I have found other DAX measures to display the compliant dates for annual courses in a table, but this % compliance is just not working.

Can someone suggest a formula that will remove all but 1 date that will refer to the Employee Training [course completed date]?

Happy to provide further information. 

As I have to put a formula into a matrix with the annual and once only courses I would also need a formula to refer to both ideally.

Thank you

Helen

[johnt75]

You'll need a column which uniquely identifies each row. If you don't have one then you can use Power Query to add an index column. In the modelling view mark that column as the key for the table.

You can then get the number of courses completed in the past year with something like

Courses completed this year =
VAR LastYear =
    DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR SummaryTable =
    INDEX (
        1,
        'Table',
        ORDERBY ( 'Table'[Completion date], DESC ),
        PARTITIONBY ( 'Table'[Employee ID] )
    )
VAR NumCoursesCompleted =
    COUNTROWS ( FILTER ( SummaryTable, 'Table'[Completion date] > LastYear ) )
RETURN
    NumCoursesCompleted

If you are going to have lots of measures looking at the most recent completion then it might be worth creating a calculated table using the INDEX function from the above code, and your measures could then refer to that.

[helenleggo]

Hi John

Thanks that makes sense. When I try to create an index column or table it is throwing an error. This is the code:

Courses this year index =

INDEX(1, 'Employee Training', ORDERBY( 'Employee Training'[Course Completed Date], DESC), KEEP, PARTITIONBY('Employee Training'[Employee He Number]))

The error is INDEX's Relation paramet may have duplicate rows. This is not allowed.

Can you suggest a fix for that? 

Thank you

Helen

[johnt75]

You'll need a column which uniquely identifies each row. If you don't have one then you can use Power Query to add an index column. In the modelling view mark that column as the key for the table.

[helenleggo]

Hi Johnt75

I tried to do that but the Employee table is a curated data set and I can't view the table in Power Query.

I have tried making a calculated column but can't get the syntax right.

Thanks anyway. Let me know if you have any other ideas, or if someone out there has any sugguestions.

Regards

Helen

[johnt75]

Try

Courses completed this year =
VAR LastYear =
    DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Course template name], 'Table'[Employee ID] ),
        "@last completed", CALCULATE ( MAX ( 'Table'[Completion date] ) )
    )
VAR NumCoursesCompleted =
    COUNTROWS ( FILTER ( SummaryTable, [@last completed] > LastYear ) )
RETURN
    NumCoursesCompleted