Calculate Attrition % (YTD) using Exit rate formula

[sailendra]

Dear Community,

I have tried calculating Attrition % YTD with the below formulae in Power BI but couldn't calculate accurately and facing errors. Can someone please help me out with the below Formula? With the below formula I have to calculate the Attrition % of different categories (like Gender, Voluntary, Location, etc)

Attrition YTD (Exit Rate) % = Cummulative Exits till month/(Active + Cummulative Exits till month) * 12/Month

For eg. Attrition YTD %

1. Feb = 1/((4+1)*12/2 = 120%
2. Mar = 3/(3+4)*12/3=171%
3. Apr = 5/(3+5)*12/4 = 187.5%

Attached here is the sample data

Month	User ID	Team	Gender	Termination Date	Date Of Joining	Active	Voluntary Attrition	Involuntary Attrition
JAN	214	ABC	Female		1-Jan-15	1	0	0
JAN	215	XYZ	Male		10-Jan-16	1	0	0
JAN	218	DEF	Male		3-Mar-17	1	0	0
JAN	219	XYZ	Female		4-Apr-15	1	0	0
JAN	220	DEF	Male		5-May-18	1	0	0
FEB	214	ABC	Female		1-Jan-15	1	0	0
FEB	215	XYZ	Male		10-Jan-16	1	0	0
FEB	218	DEF	Male	10-Feb-2021	3-Mar-17	0	1	0
FEB	219	XYZ	Female		4-Apr-15	1	0	0
FEB	220	DEF	Male		5-May-18	1	0	0
Mar	214	ABC	Female	18-Mar-2021	1-Jan-15	0	0	1
Mar	215	XYZ	Male		10-Jan-16	1	0	0
Mar	219	XYZ	Female		4-Apr-15	1	0	0
Mar	220	DEF	Male	25-Mar-2021	5-May-18	0	1	0
Mar	223	ABC	Male		5-Mar-21	1	0	0
Mar	226	XYZ	Female		10-Mar-21	1	0	0
Apr	215	XYZ	Male	20-Apr-2021	10-Jan-16	0	1	0
Apr	219	XYZ	Female	25-Apr-2021	4-Apr-15	0	0	1
Apr	223	ABC	Male		5-Mar-21	1	0	0
Apr	226	XYZ	Female		10-Mar-21	1	0	0
Apr	241	DEF	Female		20-Apr-21	1	0	0

@Ashish_Mathur, @amitchandak @v-yulgu-msft @Anonymous @Jihwan_Kim @v-jingzhang 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@sailendra , Check if my file can help. This is on my data

[sailendra]

Thank you for your response. Can you please look into the data I have shared? I find it trickier as the same employee row gets repeated every month until he is terminated and to calculate Attrition %