Status Change Rate Calculation grouping using previous dates

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.