Status Change Rate Calculation grouping using previous dates

ABR002 — Thu, 28 Sep 2023 00:34:54 GMT

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.

Re: Status Change Rate Calculation grouping using previous dates

nirali_arora — Thu, 28 Sep 2023 03:32:31 GMT

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.

topic Status Change Rate Calculation grouping using previous dates in DAX Commands and Tips

Status Change Rate Calculation grouping using previous dates

Re: Status Change Rate Calculation grouping using previous dates