How to check the status of previous month status using DAX Measure

[Arioli_Chezhian]

Hello,
Below shown is my data.

Name	Date	Status
Emp 1	01-01-2023	1
Emp 2	01-01-2023	0
Emp 1	01-02-2023	0
Emp 2	01-02-2023	0
Emp 1	01-03-2023	0
Emp 2	01-03-2023	0
Emp 1	01-04-2023	0
Emp 2	01-04-2023	0
Emp 1	01-05-2023	0
Emp 2	01-05-2023	1
Emp 1	01-06-2023	0
Emp 2	01-06-2023	0

This data shows - Employee details month wise the status of the course completion from January to June. If status = 1, then it means they have completed the course. Status = 0 then it means they haven't completed the course.

Requirement:
"Have to show Month wise percentage of employee who completed the course". 

Point to Note:
Even though the data is shown month wise, if a employee completes the course in January then even the status = 0 in february and for other months the percentage status should be considered as completed. 

Example:
As per above data, how the output should be shown is:
Jan - 50%
Feb - 50%
Mar - 50%
Apr - 50%
May - 100%
June - 100%
The emp 1 completed the course in 1st month itself, so it should be considered for rest of the month. Emp 2 completed the course in May month, so from may month the percentage of course completion will be 100%.

Please advise the DAX measure  to achieve this requirement. @amitchandak 

[danextian]

Hi @Arioli_Chezhian ,

The formula will depend on whether you wan to the result as a measure or as a calculated column. Either way, you'll need to determine  first the  earliest date the status is complete. Try these

CALC COLUMN

Earliest Month Completed (Calc Column) = 
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER (
        'Table',
        'Table'[Name] = EARLIER ( 'Table'[Name] )
            && 'Table'[Status] = 1
    )
)

Percentage (Calc Column) = 
VAR __complete =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Date] >= 'Table'[Earliest Month Completed (Calc Column)]
        )
    )
RETURN
    DIVIDE (
        __complete,
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', 'Table'[Date] = EARLIER ( 'Table'[Date] ) )
        )
    )

MEASURE

Earliest Month Completed (Measure) = 
CALCULATE (
    MIN ( 'Table'[Date] ),
    FILTER ( ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Status] = 1 )
)

Percentage (Measure) = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[Date] >= [Earliest Month Completed (Measure)] )
    ),
    COUNTROWS ( 'Table' )
)

Please see attached  pbix for the details

[TomMartens]

Hey @Arioli_Chezhian ,

to avoid complexity in the measure I create a new column "Status projected" using the below DAX statement:

Status projected = 
var currentEmployee = 'Table'[Name]
var currentStatus = 'Table'[Status]
var currentDate = 'Table'[Date]
var projectedStatus = 
    IF( currentStatus = 1
        , 1
        , var DateCompletedExists = 
            MINX(
                filter( 'Table' , 'Table'[Status] = 1 && 'Table'[Name] = currentEmployee && 'Table'[Date] < currentDate)
                , 'Table'[Date]
            )
        return
        IF( ISBLANK( DateCompletedExists) , 0 , 1)
    )
return
    projectedStatus

Basically, check if an employee has completed the course in the past, if yes it returns 1, otherwise 0. The next picture shows the result:

Then the measure becomes simple:

completed vs in-progress = 
DIVIDE(
    CALCULATE( COUNTROWS( 'Table' ) , 'Table'[Status projected] = 1 )
    , CALCULATE( COUNTROWS( 'Table' ) , ALL( 'Table'[Status] ) )
)

And the line chart:

Hopefully, this provides what you are looking for.

Regards,

Tom