Status Change Rate Calculation grouping using previous dates

[ABR002]

I'm trying to get a rate of people who have had a status change from Active to Inactive in a dataset from week to week. Example:

Name	Status	Period End Date	Week Rank
Dave	Active	6/30/2022	1
Shelly	Inactive	6/30/2022	1
Roy	Inactive	6/30/2022	1
Tom	Active	6/30/2022	1
Monica	Active	6/30/2022	1
Dave	Inactive	6/23/2022	2
Shelly	Inactive	6/23/2022	2
Roy	Inactive	6/23/2022	2
Tom	Inactive	6/23/2022	2
Monica	Active	6/23/2022	2
Dave	Active	6/16/2022	3
Shelly	Inactive	6/16/2022	3
Roy	Inactive	6/16/2022	3
Tom	Active	6/16/2022	3
Monica	Active	6/16/2022	3

Basically Where employee Status = "active" AND week rank = Minimum / Status = "inactive" where week rank = 1 above the minimum
It's essentially the 1 above the minimum that's killing me. Can do the other parts .
Any advice would be appreciated. 

[nirali_arora · Accepted solution]

1. Create two calculated tables to filter "Active" and "Inactive" employees for each week rank.

   ActiveEmployees = FILTER(EmployeeStatus, EmployeeStatus[Status] = "Active") InactiveEmployees = FILTER(EmployeeStatus, EmployeeStatus[Status] = "Inactive")

2. Create a calculated column that calculates the minimum WeekRank for each employee.

   MinWeekRank = CALCULATE(MIN(EmployeeStatus[WeekRank]), ALLEXCEPT(EmployeeStatus, EmployeeStatus[EmployeeID]))

3. Create a calculated column to identify employees who transitioned from "Active" to "Inactive" and have WeekRank 1 above the minimum.

   TransitionedToInactive = IF ( EmployeeStatus[Status] = "Active" && EmployeeStatus[WeekRank] = [MinWeekRank] + 1 && COUNTROWS( FILTER( InactiveEmployees, InactiveEmployees[EmployeeID] = EmployeeStatus[EmployeeID] ) ) > 0, 1, 0 )

4. Create a measure to calculate the rate of transitions:

   TransitionRate = DIVIDE( SUM(EmployeeStatus[TransitionedToInactive]), COUNTROWS(EmployeeStatus) )

This measure, "TransitionRate," will give you the rate of employees who transitioned from "Active" to "Inactive" with WeekRank 1 above the minimum WeekRank.