Forum Discussion
Status Change Rate Calculation grouping using previous dates
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.
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")Create a calculated column that calculates the minimum WeekRank for each employee.
MinWeekRank = CALCULATE(MIN(EmployeeStatus[WeekRank]), ALLEXCEPT(EmployeeStatus, EmployeeStatus[EmployeeID]))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 )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.
1 Reply
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")Create a calculated column that calculates the minimum WeekRank for each employee.
MinWeekRank = CALCULATE(MIN(EmployeeStatus[WeekRank]), ALLEXCEPT(EmployeeStatus, EmployeeStatus[EmployeeID]))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 )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.