cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

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.

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 3
Super User

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

Regular Visitor

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
Super User

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 _AvgHC =
AVERAGEX(
)
VAR _exit =
COUNTROWS(
)
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.