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 comp...
FreemanZ
Super User
Super Userhi 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.