Employee Tenure Band Calculate

[ugurozdemir]

Hi, 

Hi, I have a table like the one below.

What I want to do is calculate the tenure banding (1-3 Year xxx employee, 3-5 year xxx employee exc.) for both the leavers and the employees.

I would be glad if you can assist.

Tenure Employee = CALCULATE(AVERAGEX('Turn Over', ROUNDDOWN(DATEDIFF('Turn Over'[Hire Date],NOW(),MONTH)/12,0))FILTER('Turn Over','Turn Over'[Employee]=1))

Tenure Term = CALCULATE(AVERAGEX('Turn Over', ROUNDDOWN(DATEDIFF('Turn Over'[Term Date],'Turn Over','Turn Over'[Term Date],MONTH)/12,0))FILTER('Turn Over','Turn Over'[Employee]=0))

how can I combine them? Or how i calculate in power query?

Hire Date	Term Date
29.01.2021	14.03.2021
10.01.2020
17.08.2020
7.01.2020	26.03.2020
10.07.2020
10.12.2018
25.02.2015

[ugurozdemir]

Thank you @amitchandak 

[amitchandak · Accepted solution]

@ugurozdemir , Check the attached file. I have done this on my data. I do have created age Bucket