Forum Discussion
YTD attrition % calculation
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%
3 Replies
FreemanZSuper User
hi Vishal7012
What relevant columns do you have in both headcount and attrition table?
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)*12With 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.
