Calculating Employees Average Tenure for Each Year

[Nzadeh]

Hello BI Community!

I want to calculate employees' average tenure for each year and if possible average tenure for each gender and ethnicity group. I appreciate it if someone could help me with this. Here are the dataset and the pbix file. 

pbix: https://drive.google.com/file/d/1P_aQGCShF-tVLIlprMN91yAkm8NS7xot/view?usp=sharing

dataset: https://docs.google.com/spreadsheets/d/1O7TxoapNr6_OA1MKW9l_EGH4DHqKzawL/edit?usp=sharing&ouid=11229...

[v-jayw-msft]

Hi @Nzadeh ,

Have trouble to follow, do you want datediff/years or (sum of tenure/ count of Employees) each year?

Best Regards,

Jay

[amitchandak]

@Nzadeh , Try a Meausre like

Avg Stay =
CALCULATE(AVERAGEX(FILTER(Employees,Employees[Hire_Date]<=MAX(Dates[Date]) && (ISBLANK(Employees[Termination_Date]) || Employees[Termination_Date]>MAX(Dates[Date]))), DATEDIFF([Hire_Date],if(ISBLANK(Employees[Termination_Date]) || Employees[Termination_Date]>MAX(Dates[Date]), max('Dates'[Date]),Employees[Termination_Date]), MONTH )))

Or a column, that will always give based on today or termination date

Tenure = datediff([Hire_Date], COALESCE([Termination_Date], TODAY()),MONTH)

and then take avg

[Nzadeh]

Thank you so much @amitchandak ! I used both of these options, but I got different results.