YTD attrition % calculation

[Vishal7012]

Hi,

I am looking for the Year to Date (YTD) attrition % DAX as per the below formula. Year is from October - September.

YTD attrition % = ((No. of exits / Average Headcount) / No. of months completed in the year) * 12

No. of exits = Exits from 1-Oct till date

Average Headcount = Average of every month's headcount

No. of months completed in the year  = For eg. it is 3 as of today i.e. (October - December). This should be dynamic post every month completion

There are 2 separate tables for Headcount & attrition. While headcount table will have repetitive entries every month, attrition table will have unique employee data.

Headcount & Attrition tables will be uploaded on a monthly basis.

Example of the expected result: 

YTD % (October) = ((10/500)/1)*12 & the result is 24%.

YTD % (November) = ((12/504)/2)*12 & the result is 14%

YTD % (December) = ((16/502)/3)*12 & the result is 13%

[FreemanZ]

hi @Vishal7012 

What relevant columns do you have in both headcount and attrition table?

[Vishal7012]

Hi @FreemanZ ,

Here are the samples of the 2 tables i'm using. Hope this helps.

1. Headcount table sample for every month. Will be uploaded every month.

Employee ID	Emp Name	Joining date	Upload Month
5001	A	1-Jan-02	Oct-22
5002	B	1-Mar-15	Oct-22
5003	C	1-Jun-22	Oct-22
5004	D	1-Sep-22	Oct-22
			Oct-22
5500	E	1-Jul-10	Oct-22
5001	A	1-Jan-02	Nov-22
5002	B	1-Mar-15	Nov-22
5003	C	1-Jun-22	Nov-22
5004	D	1-Sep-22	Nov-22
			Nov-22
5504	G	1-Jul-21	Nov-22
5001	A	1-Jan-02	Dec-22
5002	B	1-Mar-15	Dec-22
5003	C	1-Jun-22	Dec-22
5004	D	1-Sep-22	Dec-22
			Dec-22
5502	K	1-Jul-21	Dec-22

2. Attrition Table sample. Will be uploaded every month.

Employee ID	Emp Name	Last working date	Upload month
1001	X	1-Oct-22	Oct-22
1002	Y	10-Oct-22	Oct-22
1003	Z	11-Oct-22	Oct-22
1004	ZA	12-Oct-22	Oct-22
1005	ZB	13-Oct-22	Oct-22
1006	ZX	14-Oct-22	Oct-22
1007	ZAA	15-Oct-22	Oct-22
1008	ZBB	16-Oct-22	Oct-22
1009	ZXX	17-Oct-22	Oct-22
1010	ZAP	18-Oct-22	Oct-22
2001	ZBB	1-Nov-22	Nov-22
2002	ZXX	2-Nov-22	Nov-22
2003	ZAP	3-Nov-22	Nov-22
2004	ZBB	4-Nov-22	Nov-22
2005	ZXX	5-Nov-22	Nov-22
2006	ZAP	6-Nov-22	Nov-22
2007	ZBB	7-Nov-22	Nov-22
2008	ZXX	8-Nov-22	Nov-22
2009	ZAP	9-Nov-22	Nov-22
2010	ZBB	10-Nov-22	Nov-22
2011	ZXX	11-Nov-22	Nov-22
2012	ZAP	12-Nov-22	Nov-22
30001	ZXX	1-Dec-22	Dec-22
30002	ZAP	2-Dec-22	Dec-22
30003	ZXX	3-Dec-22	Dec-22
30004	ZAP	4-Dec-22	Dec-22
30005	ZXX	5-Dec-22	Dec-22
30006	ZAP	6-Dec-22	Dec-22
30007	ZXX	7-Dec-22	Dec-22
30008	ZAP	8-Dec-22	Dec-22
30009	ZXX	9-Dec-22	Dec-22
30010	ZAP	10-Dec-22	Dec-22
30011	ZXX	11-Dec-22	Dec-22
30012	ZAP	12-Dec-22	Dec-22
30013	ZXX	13-Dec-22	Dec-22
30014	ZAP	14-Dec-22	Dec-22
30015	ZXX	15-Dec-22	Dec-22
30016	ZAP	16-Dec-22	Dec-22

[FreemanZ]

hi @Vishal7012 

It hides some complexity:

1) suggest to add a column to convert the upload month to date, like Oct-2022 to 2022/10/01. 

2) plot a visual with the upload month and a measure like this:

Attrition% = 
VAR _month = MIN(Attrition[Upload month])
VAR _AvgHC = 
AVERAGEX(
    VALUES( HeadCount[Upload Month]),
    CALCULATE(COUNTROWS(HeadCount))
)
VAR _exit =
COUNTROWS(
    FILTER(ALL(Attrition), Attrition[Upload month]<=_month)
)
VAR _n = DATEDIFF(DATE(2022,10,1), _month, MONTH)+1
RETURN
(_exit/_AvgHC/_n)*12

With your sample data, it worked like this:

the % is huge, but consistent with your sample data. 

p.s. plot with the upload month directly, not the date hierarchy.