Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Attrition Rate of Hires

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 
Capture.JPG

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?

 

1 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] ) )
            )
    )

3.png

 

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi, 

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)

PeriodEmp_iDHire DateLeave Date
1/7/20172011402/2/200411/7/2017
1/7/201720310410/6/201314/7/2017
1/7/20172035224/8/20156/7/2017
1/7/20172035254/8/20153/7/2017
1/7/20172036885/9/20167/7/2017
1/7/201720380120/3/201718/7/2017
1/7/20172038085/4/20173/7/2017
1/8/20172020754/9/20074/8/2017
1/8/201720323920/12/201331/8/2017
1/8/201720332215/12/201418/8/2017
1/8/20172035741/10/201530/8/2017
1/8/201720375713/12/201631/8/2017
1/8/20172037635/1/201725/8/2017
1/8/201720376920/1/201718/8/2017
1/9/20172003381/11/199929/9/2017
1/9/20172010442/6/200313/9/2017
1/9/20172017611/9/200629/9/2017
1/9/201720311325/6/20136/9/2017
1/9/20172032911/9/20148/9/2017
1/9/201720330323/10/201429/9/2017
1/9/201720344719/5/20155/9/2017
1/9/201720350314/7/201511/9/2017
1/9/20172036865/9/201615/9/2017
1/9/201720370920/10/20168/9/2017
1/9/201720373616/11/20164/9/2017
1/9/20172037479/12/201619/9/2017
1/9/20172037605/1/201725/9/2017
1/9/20172038133/7/20178/9/2017
1/9/20172038143/7/20178/9/2017
1/9/20172038153/7/20178/9/2017
1/9/20172038163/7/20178/9/2017
1/9/20172038173/7/20178/9/2017
1/9/20172038183/7/20178/9/2017
1/9/20172038266/9/20178/9/2017
1/9/20172038306/9/201712/9/2017
1/9/201720384121/9/201725/9/2017
1/9/201720384221/9/201721/9/2017
1/9/20178000131/11/199029/9/2017
1/9/20178000151/9/199629/9/2017
1/10/201720074022/8/20012/10/2017
1/10/201720217319/11/200713/10/2017
1/10/201720231416/5/200831/10/2017
1/10/201720344219/5/20156/10/2017
1/10/201720349814/7/201525/10/2017
1/10/201720350414/7/201523/10/2017
1/10/20172036263/11/20152/10/2017
1/10/201720363510/11/201525/10/2017
1/10/201720365415/3/20166/10/2017
1/10/20172037645/1/20176/10/2017
1/10/201720379220/3/201720/10/2017
1/10/201720379720/3/20172/10/2017
1/11/201720209318/7/201128/11/2017
1/11/20172030243/12/201217/11/2017
1/11/20172033529/3/20151/11/2017
1/11/201720341221/4/201517/11/2017
1/11/201720373116/11/201616/11/2017
1/11/201720373316/11/201616/11/2017
1/11/20172037767/2/20171/11/2017
1/11/20172038479/11/201723/11/2017
1/12/201720041210/7/200029/12/2017
1/12/20172008123/12/20018/12/2017
1/12/201720292726/8/201320/12/2017
1/12/20172032921/9/201429/12/2017
1/12/201720330817/11/201429/12/2017
1/12/201720346026/5/201519/12/2017
1/12/20172034891/7/201515/12/2017
1/12/201720355017/9/201521/12/2017
1/12/20172036531/3/201629/12/2017
1/12/20172037669/1/201729/12/2017
1/12/20172037787/2/201713/12/2017
1/12/20172037901/3/201715/12/2017
1/12/20172037911/3/201713/12/2017
1/12/20172038276/9/20174/12/2017
1/1/201820082410/12/200126/1/2018
1/1/201820315210/10/20132/1/2018
1/1/201820333118/2/20152/1/2018
1/1/201820336017/3/201526/1/2018
1/1/201820340121/4/20152/1/2018
1/1/20182036609/5/20162/1/2018
1/1/201820366212/5/201631/1/2018
1/1/201820380521/3/201726/1/2018
1/4/201820109911/11/200320/4/2018
1/4/201820144014/9/200527/4/2018
1/4/201820306822/4/201320/4/2018
1/4/20182030963/6/201310/4/2018
1/4/201820324320/12/201320/4/2018
1/4/201820342022/4/20152/4/2018
1/4/201820343713/5/201530/4/2018
1/4/201820366621/6/201625/4/2018
1/4/201820370720/10/201630/4/2018
1/4/20182038256/9/201718/4/2018
1/4/201820383618/9/201712/4/2018
1/4/201820383718/9/201710/4/2018
1/4/201820383818/9/201712/4/2018
1/4/201820385423/1/20182/4/2018
1/7/20182024481/8/200819/7/2018
1/7/201820352911/8/20154/7/2018
1/7/201820380221/3/201727/7/2018
1/7/20182038316/9/201720/7/2018
1/8/20182003498/11/199927/8/2018
1/8/20182012037/6/200427/8/2018
1/8/201820128824/1/200527/8/2018
1/8/201820135121/4/200527/8/2018
1/8/20182019941/6/200731/8/2018
1/8/20182025168/9/20083/8/2018
1/8/20182028788/10/200927/8/2018
1/8/20182030253/12/20123/8/2018
1/8/201820339421/4/201528/8/2018
1/8/20182036464/1/201631/8/2018
1/8/201820379520/3/20177/8/2018
1/8/201820383921/9/20171/8/2018
1/9/201820116224/2/20045/9/2018
1/9/20182012641/12/200428/9/2018
1/9/201820198725/5/200721/9/2018
1/9/20182023861/7/200811/9/2018
1/9/201820243324/7/20083/9/2018
1/9/20182025011/9/200828/9/2018
1/9/201820271010/11/20085/9/2018
1/9/20182030221/11/201228/9/2018
1/9/201820324020/12/201328/9/2018
1/9/201820324910/2/201428/9/2018
1/9/201820325822/4/201428/9/2018
1/9/20182035721/10/20151/9/2018
1/9/201820360914/10/201513/9/2018
1/9/201820364318/1/201628/9/2018
1/9/201820364825/1/201628/9/2018
1/9/20182036528/2/201628/9/2018
1/9/20182036557/3/201628/9/2018
1/9/201820367812/9/201628/9/2018
1/9/20182037003/10/20166/9/2018
1/9/20182037171/11/20167/9/2018
1/9/20182038504/12/20176/9/2018
1/9/20182038622/7/201828/9/2018
1/9/20182038632/7/201828/9/2018
1/9/20182038642/7/201828/9/2018
1/9/20182038652/7/201828/9/2018
1/9/20182038662/7/201828/9/2018
1/9/20182038672/7/201828/9/2018
1/10/20182001629/7/199725/10/2018
1/10/20182007882/11/200125/10/2018
1/10/201820243617/3/20141/10/2018
1/10/20182025801/10/200812/10/2018
1/10/201820300824/5/20115/10/2018
1/10/201820300922/8/20115/10/2018
1/10/201820324420/12/201319/10/2018
1/10/20182036253/11/201525/10/2018
1/10/20182036424/1/201619/10/2018
1/10/201820367311/7/201631/10/2018
1/10/201820368919/9/201610/10/2018
1/10/201820369519/9/201625/10/2018
1/10/20182037227/11/20161/10/2018
1/10/20182038513/1/201831/10/2018
1/10/201820387520/8/201816/10/2018
1/11/20182019251/3/200723/11/2018
1/11/20182025731/10/20082/11/2018
1/11/20182031003/6/20139/11/2018
1/11/201820328221/7/201430/11/2018
1/11/201820347422/6/20152/11/2018
1/11/20182036391/12/20152/11/2018
1/11/20182036651/6/201614/11/2018
1/11/20182036825/9/201622/11/2018
1/11/20182036855/9/20162/11/2018
1/11/201820370210/10/201630/11/2018
1/11/201820373416/11/20167/11/2018
1/11/201820382121/8/201714/11/2018
1/11/201820382411/9/20172/11/2018
1/12/20182010473/3/20086/12/2018
1/12/20182013593/5/20057/12/2018
1/12/20182018688/12/200613/12/2018
1/12/20182021913/12/200713/12/2018
1/12/20182030123/10/201131/12/2018
1/12/201820307522/4/201313/12/2018
1/12/201820312515/7/20136/12/2018
1/12/201820318125/11/20136/12/2018
1/12/201820343813/5/201521/12/2018
1/12/201820345326/5/20155/12/2018
1/12/20182034648/6/201531/12/2018
1/12/201820346716/6/20156/12/2018
1/12/201820349714/7/201519/12/2018
1/12/20182036574/5/201621/12/2018
1/12/20182036584/5/201621/12/2018
1/12/20182036983/10/201628/12/2018
1/12/201820370110/10/201621/12/2018
1/12/201820384321/9/201714/12/2018
1/12/201820387420/8/201821/12/2018
1/12/201820388717/12/201821/12/2018
1/1/20192014941/11/200525/1/2019
1/1/20192029304/1/201016/1/2019
1/1/201920308113/5/201323/1/2019
1/1/201920346226/5/201510/1/2019
1/1/201920371420/10/201631/1/2019
1/1/20192037161/11/20164/1/2019
1/1/20192037797/2/201731/1/2019
1/1/201920383318/9/201725/1/2019
1/1/201920389117/12/201818/1/2019
1/2/201920181823/10/200618/2/2019
1/2/20192024961/9/200828/2/2019
1/2/201920328725/8/201422/2/2019
1/2/20192032901/9/201415/2/2019
1/2/20192033469/3/201518/2/2019
1/2/201920348022/6/20151/2/2019
1/2/201920348424/6/201518/2/2019
1/2/20192038469/11/201713/2/2019
1/2/20192038691/8/201815/2/2019
1/3/20192023801/7/200822/3/2019
1/3/201920342612/5/201515/3/2019
1/3/201920369019/9/201629/3/2019
1/3/201920389417/12/20186/3/2019
1/3/201920389718/1/201922/3/2019
1/4/20192028555/8/200930/4/2019
1/4/201920347815/6/201525/4/2019
1/4/20192037459/12/201617/4/2019
1/4/201920380421/3/20175/4/2019
1/4/201920384021/9/201723/4/2019
1/4/20192038612/5/20181/4/2019

 

Vassilis

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] ) )
            )
    )

3.png

 

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors