Count training records completed in a date range.

[amtrakrob]

I’m running a training program that consists of 5 groups of classes.
I need to check if an employee completed that group of classes between 1/1/2024 and 4/7/2024

I suspect I need to do some type of group by (probably nested), but I can’t figure it out.

This example is just one group of classes.

Completed Example
EmployeeID	Class ID	Completed Date	Group Status
1234	A	1/1/2024	Completed
1234	B	1/10/2024	Completed
1234	C	2/20/2024	Completed
1234	D	4/7/2024	Completed
Incomplete Example
EmployeeID	Class ID	Completed Date	Status
1234	A	1/1/2024	Incomplete
1234	B	1/10/2024	Incomplete
1234	C	2/20/2024	Incomplete
1234	D	4/15/2024	Incomplete

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check below pictures and the attached pbix file.

Status measure: =
VAR _selectedclasses =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( Data, Class[Class ID] ),
            REMOVEFILTERS ( 'Calendar'[Date] )
        )
    )
VAR _completedclasses =
    COUNTROWS ( SUMMARIZE ( Data, Class[Class ID] ) )
RETURN
    IF (
        HASONEVALUE ( Employee[EmployeeID] ),
        IF ( _selectedclasses > _completedclasses, "Incomplete", "Complete" )
    )