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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors