Attrition Rate of Hires

[Anonymous]

Hello,

I am trying to create an Attrition rate for hires, meanining from 2017 which hired employees left in 2017,2018 and 2019

The 1st year it was easy to find :

(CALCULATE([Leavers];filter(BasicData;[isNewHire_Annual])))

*where Leavers are all employees with leave date and New Hire Annual is a column were the date's year matches the Hire Date' year. 

For the next years I have created at my basic data table 3 columns

Hired 2017_Left 2018 = IF(AND(BasicData[Leave Date].[Year]=2018;BasicData[Hire Date].[Year]=2017);1)

Hired 2017_Left 2019 = IF(AND(BasicData[Leave Date].[Year]=2019;BasicData[Hire Date].[Year]=2017);1)

Hired 2018_Left 2019 = IF(AND(BasicData[Leave Date].[Year]=2019;BasicData[Hire Date].[Year]=2018);1)

Correct Data visual should be:

	Hires	Left within 1st Year	Left in 2nd Year	Left in 3rd Year
2017	92	31	17	5
2018	43	13	4	0
2019	15	1	0	0

But the visual I get is 


Hires of 2017 who left in 2018 are demonstrated in 2018, where it should be 2017 and those who left in 2019, again in 2017 (place based on their hire date)

Could you provide some insight?

[Anonymous]

Have anyone found the fficial definitions used by PowerBi Admins / Creator?

Are those voluntary or not? How attrition is differentiated from turnover?

[v-shex-msft]

HI @Anonymous,

In my opinion, you only need one calculated column to compare current employee's hire and leave date to return tag, then use it on matrix visual column fields.
Can you please share some sample data for test?

Notice: do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, v-shex-msft

Since i do not have the option to use file sharing sites, please find a sample of the data (using excel to upload data to BI) below (sorry for the long post)

Period	Emp_iD	Hire Date	Leave Date
1/7/2017	201140	2/2/2004	11/7/2017
1/7/2017	203104	10/6/2013	14/7/2017
1/7/2017	203522	4/8/2015	6/7/2017
1/7/2017	203525	4/8/2015	3/7/2017
1/7/2017	203688	5/9/2016	7/7/2017
1/7/2017	203801	20/3/2017	18/7/2017
1/7/2017	203808	5/4/2017	3/7/2017
1/8/2017	202075	4/9/2007	4/8/2017
1/8/2017	203239	20/12/2013	31/8/2017
1/8/2017	203322	15/12/2014	18/8/2017
1/8/2017	203574	1/10/2015	30/8/2017
1/8/2017	203757	13/12/2016	31/8/2017
1/8/2017	203763	5/1/2017	25/8/2017
1/8/2017	203769	20/1/2017	18/8/2017
1/9/2017	200338	1/11/1999	29/9/2017
1/9/2017	201044	2/6/2003	13/9/2017
1/9/2017	201761	1/9/2006	29/9/2017
1/9/2017	203113	25/6/2013	6/9/2017
1/9/2017	203291	1/9/2014	8/9/2017
1/9/2017	203303	23/10/2014	29/9/2017
1/9/2017	203447	19/5/2015	5/9/2017
1/9/2017	203503	14/7/2015	11/9/2017
1/9/2017	203686	5/9/2016	15/9/2017
1/9/2017	203709	20/10/2016	8/9/2017
1/9/2017	203736	16/11/2016	4/9/2017
1/9/2017	203747	9/12/2016	19/9/2017
1/9/2017	203760	5/1/2017	25/9/2017
1/9/2017	203813	3/7/2017	8/9/2017
1/9/2017	203814	3/7/2017	8/9/2017
1/9/2017	203815	3/7/2017	8/9/2017
1/9/2017	203816	3/7/2017	8/9/2017
1/9/2017	203817	3/7/2017	8/9/2017
1/9/2017	203818	3/7/2017	8/9/2017
1/9/2017	203826	6/9/2017	8/9/2017
1/9/2017	203830	6/9/2017	12/9/2017
1/9/2017	203841	21/9/2017	25/9/2017
1/9/2017	203842	21/9/2017	21/9/2017
1/9/2017	800013	1/11/1990	29/9/2017
1/9/2017	800015	1/9/1996	29/9/2017
1/10/2017	200740	22/8/2001	2/10/2017
1/10/2017	202173	19/11/2007	13/10/2017
1/10/2017	202314	16/5/2008	31/10/2017
1/10/2017	203442	19/5/2015	6/10/2017
1/10/2017	203498	14/7/2015	25/10/2017
1/10/2017	203504	14/7/2015	23/10/2017
1/10/2017	203626	3/11/2015	2/10/2017
1/10/2017	203635	10/11/2015	25/10/2017
1/10/2017	203654	15/3/2016	6/10/2017
1/10/2017	203764	5/1/2017	6/10/2017
1/10/2017	203792	20/3/2017	20/10/2017
1/10/2017	203797	20/3/2017	2/10/2017
1/11/2017	202093	18/7/2011	28/11/2017
1/11/2017	203024	3/12/2012	17/11/2017
1/11/2017	203352	9/3/2015	1/11/2017
1/11/2017	203412	21/4/2015	17/11/2017
1/11/2017	203731	16/11/2016	16/11/2017
1/11/2017	203733	16/11/2016	16/11/2017
1/11/2017	203776	7/2/2017	1/11/2017
1/11/2017	203847	9/11/2017	23/11/2017
1/12/2017	200412	10/7/2000	29/12/2017
1/12/2017	200812	3/12/2001	8/12/2017
1/12/2017	202927	26/8/2013	20/12/2017
1/12/2017	203292	1/9/2014	29/12/2017
1/12/2017	203308	17/11/2014	29/12/2017
1/12/2017	203460	26/5/2015	19/12/2017
1/12/2017	203489	1/7/2015	15/12/2017
1/12/2017	203550	17/9/2015	21/12/2017
1/12/2017	203653	1/3/2016	29/12/2017
1/12/2017	203766	9/1/2017	29/12/2017
1/12/2017	203778	7/2/2017	13/12/2017
1/12/2017	203790	1/3/2017	15/12/2017
1/12/2017	203791	1/3/2017	13/12/2017
1/12/2017	203827	6/9/2017	4/12/2017
1/1/2018	200824	10/12/2001	26/1/2018
1/1/2018	203152	10/10/2013	2/1/2018
1/1/2018	203331	18/2/2015	2/1/2018
1/1/2018	203360	17/3/2015	26/1/2018
1/1/2018	203401	21/4/2015	2/1/2018
1/1/2018	203660	9/5/2016	2/1/2018
1/1/2018	203662	12/5/2016	31/1/2018
1/1/2018	203805	21/3/2017	26/1/2018
1/4/2018	201099	11/11/2003	20/4/2018
1/4/2018	201440	14/9/2005	27/4/2018
1/4/2018	203068	22/4/2013	20/4/2018
1/4/2018	203096	3/6/2013	10/4/2018
1/4/2018	203243	20/12/2013	20/4/2018
1/4/2018	203420	22/4/2015	2/4/2018
1/4/2018	203437	13/5/2015	30/4/2018
1/4/2018	203666	21/6/2016	25/4/2018
1/4/2018	203707	20/10/2016	30/4/2018
1/4/2018	203825	6/9/2017	18/4/2018
1/4/2018	203836	18/9/2017	12/4/2018
1/4/2018	203837	18/9/2017	10/4/2018
1/4/2018	203838	18/9/2017	12/4/2018
1/4/2018	203854	23/1/2018	2/4/2018
1/7/2018	202448	1/8/2008	19/7/2018
1/7/2018	203529	11/8/2015	4/7/2018
1/7/2018	203802	21/3/2017	27/7/2018
1/7/2018	203831	6/9/2017	20/7/2018
1/8/2018	200349	8/11/1999	27/8/2018
1/8/2018	201203	7/6/2004	27/8/2018
1/8/2018	201288	24/1/2005	27/8/2018
1/8/2018	201351	21/4/2005	27/8/2018
1/8/2018	201994	1/6/2007	31/8/2018
1/8/2018	202516	8/9/2008	3/8/2018
1/8/2018	202878	8/10/2009	27/8/2018
1/8/2018	203025	3/12/2012	3/8/2018
1/8/2018	203394	21/4/2015	28/8/2018
1/8/2018	203646	4/1/2016	31/8/2018
1/8/2018	203795	20/3/2017	7/8/2018
1/8/2018	203839	21/9/2017	1/8/2018
1/9/2018	201162	24/2/2004	5/9/2018
1/9/2018	201264	1/12/2004	28/9/2018
1/9/2018	201987	25/5/2007	21/9/2018
1/9/2018	202386	1/7/2008	11/9/2018
1/9/2018	202433	24/7/2008	3/9/2018
1/9/2018	202501	1/9/2008	28/9/2018
1/9/2018	202710	10/11/2008	5/9/2018
1/9/2018	203022	1/11/2012	28/9/2018
1/9/2018	203240	20/12/2013	28/9/2018
1/9/2018	203249	10/2/2014	28/9/2018
1/9/2018	203258	22/4/2014	28/9/2018
1/9/2018	203572	1/10/2015	1/9/2018
1/9/2018	203609	14/10/2015	13/9/2018
1/9/2018	203643	18/1/2016	28/9/2018
1/9/2018	203648	25/1/2016	28/9/2018
1/9/2018	203652	8/2/2016	28/9/2018
1/9/2018	203655	7/3/2016	28/9/2018
1/9/2018	203678	12/9/2016	28/9/2018
1/9/2018	203700	3/10/2016	6/9/2018
1/9/2018	203717	1/11/2016	7/9/2018
1/9/2018	203850	4/12/2017	6/9/2018
1/9/2018	203862	2/7/2018	28/9/2018
1/9/2018	203863	2/7/2018	28/9/2018
1/9/2018	203864	2/7/2018	28/9/2018
1/9/2018	203865	2/7/2018	28/9/2018
1/9/2018	203866	2/7/2018	28/9/2018
1/9/2018	203867	2/7/2018	28/9/2018
1/10/2018	200162	9/7/1997	25/10/2018
1/10/2018	200788	2/11/2001	25/10/2018
1/10/2018	202436	17/3/2014	1/10/2018
1/10/2018	202580	1/10/2008	12/10/2018
1/10/2018	203008	24/5/2011	5/10/2018
1/10/2018	203009	22/8/2011	5/10/2018
1/10/2018	203244	20/12/2013	19/10/2018
1/10/2018	203625	3/11/2015	25/10/2018
1/10/2018	203642	4/1/2016	19/10/2018
1/10/2018	203673	11/7/2016	31/10/2018
1/10/2018	203689	19/9/2016	10/10/2018
1/10/2018	203695	19/9/2016	25/10/2018
1/10/2018	203722	7/11/2016	1/10/2018
1/10/2018	203851	3/1/2018	31/10/2018
1/10/2018	203875	20/8/2018	16/10/2018
1/11/2018	201925	1/3/2007	23/11/2018
1/11/2018	202573	1/10/2008	2/11/2018
1/11/2018	203100	3/6/2013	9/11/2018
1/11/2018	203282	21/7/2014	30/11/2018
1/11/2018	203474	22/6/2015	2/11/2018
1/11/2018	203639	1/12/2015	2/11/2018
1/11/2018	203665	1/6/2016	14/11/2018
1/11/2018	203682	5/9/2016	22/11/2018
1/11/2018	203685	5/9/2016	2/11/2018
1/11/2018	203702	10/10/2016	30/11/2018
1/11/2018	203734	16/11/2016	7/11/2018
1/11/2018	203821	21/8/2017	14/11/2018
1/11/2018	203824	11/9/2017	2/11/2018
1/12/2018	201047	3/3/2008	6/12/2018
1/12/2018	201359	3/5/2005	7/12/2018
1/12/2018	201868	8/12/2006	13/12/2018
1/12/2018	202191	3/12/2007	13/12/2018
1/12/2018	203012	3/10/2011	31/12/2018
1/12/2018	203075	22/4/2013	13/12/2018
1/12/2018	203125	15/7/2013	6/12/2018
1/12/2018	203181	25/11/2013	6/12/2018
1/12/2018	203438	13/5/2015	21/12/2018
1/12/2018	203453	26/5/2015	5/12/2018
1/12/2018	203464	8/6/2015	31/12/2018
1/12/2018	203467	16/6/2015	6/12/2018
1/12/2018	203497	14/7/2015	19/12/2018
1/12/2018	203657	4/5/2016	21/12/2018
1/12/2018	203658	4/5/2016	21/12/2018
1/12/2018	203698	3/10/2016	28/12/2018
1/12/2018	203701	10/10/2016	21/12/2018
1/12/2018	203843	21/9/2017	14/12/2018
1/12/2018	203874	20/8/2018	21/12/2018
1/12/2018	203887	17/12/2018	21/12/2018
1/1/2019	201494	1/11/2005	25/1/2019
1/1/2019	202930	4/1/2010	16/1/2019
1/1/2019	203081	13/5/2013	23/1/2019
1/1/2019	203462	26/5/2015	10/1/2019
1/1/2019	203714	20/10/2016	31/1/2019
1/1/2019	203716	1/11/2016	4/1/2019
1/1/2019	203779	7/2/2017	31/1/2019
1/1/2019	203833	18/9/2017	25/1/2019
1/1/2019	203891	17/12/2018	18/1/2019
1/2/2019	201818	23/10/2006	18/2/2019
1/2/2019	202496	1/9/2008	28/2/2019
1/2/2019	203287	25/8/2014	22/2/2019
1/2/2019	203290	1/9/2014	15/2/2019
1/2/2019	203346	9/3/2015	18/2/2019
1/2/2019	203480	22/6/2015	1/2/2019
1/2/2019	203484	24/6/2015	18/2/2019
1/2/2019	203846	9/11/2017	13/2/2019
1/2/2019	203869	1/8/2018	15/2/2019
1/3/2019	202380	1/7/2008	22/3/2019
1/3/2019	203426	12/5/2015	15/3/2019
1/3/2019	203690	19/9/2016	29/3/2019
1/3/2019	203894	17/12/2018	6/3/2019
1/3/2019	203897	18/1/2019	22/3/2019
1/4/2019	202855	5/8/2009	30/4/2019
1/4/2019	203478	15/6/2015	25/4/2019
1/4/2019	203745	9/12/2016	17/4/2019
1/4/2019	203804	21/3/2017	5/4/2019
1/4/2019	203840	21/9/2017	23/4/2019
1/4/2019	203861	2/5/2018	1/4/2019

Vassilis

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can refer to below formulas to get correspond output:

Calculate column:

Work Year = DATEDIFF([Hire Date],[Leave Date],YEAR)

Measure formula:

Hire = 
CALCULATE (
    COUNT ( Table1[Emp_iD] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        YEAR ( Table1[Hire Date] ) = YEAR ( MAX ( Table1[Period] ) )
    )
)

leaved new Hire = 
CALCULATE (
    COUNT ( Table1[Emp_iD] ),
    FILTER (
        ALLSELECTED ( Table1 ),
        YEAR ( Table1[Hire Date] )
            = YEAR (
                MAX ( Table1[Period] ))
                    && YEAR ( Table1[Leave Date] ) = YEAR ( MAX ( Table1[Period] ) )
            )
    )

Notice: work year 0 means hire and leave in one year.

Regards,

Xiaoxin Sheng