Calculate the average employee tenure in months for latest role

Chris2016 — Fri, 07 Apr 2023 14:28:22 GMT

Hello,

Could you please help with the following issue? I have to calculate the average of employee tenure in months for latest role in a table where each individual has multiple rows for role date start.

Name	CompanyStartDate	RoleDateTime	_Months in latest Role	_Employee Tenure
A	11/9/2020 0:00	12/1/2021 0:00	5	3-6 Months
A	11/9/2020 0:00	6/30/2022 0:00	5	3-6 Months
A	11/9/2020 0:00	8/15/2022 0:00	5	3-6 Months
A	11/9/2020 0:00	11/11/2022 0:00	5	3-6 Months
B	7/5/2021 0:00	12/1/2021 0:00	7	6-12 months
B	7/5/2021 0:00	9/1/2022 0:00	7	6-12 months
B	7/5/2021 0:00	9/12/2022 0:00	7	6-12 months
C	3/1/2021 0:00	12/1/2021 0:00	9	6-12 months
C	3/1/2021 0:00	7/4/2022 0:00	9	6-12 months
D	12/27/2021 0:00	6/14/2022 0:00	10	6-12 months
F	10/4/2021 0:00	10/4/2021 0:00	13	12-24 Months
F	10/4/2021 0:00	3/1/2022 0:00	13	12-24 Months

I calculated the employee's months in role for their latest role start, but how can I calculate the average for the entire table?

_Months in latest Role = 
var currDate = CALCULATE(MAX([RoleDateTime]), ALLEXCEPT('Table', 'Table'[Name]))
return  ABS ( DATEDIFF ( currDate, TODAY (), MONTH ) )

The average in the above table should be 8.8, but how can I calculate it in DAX?

Thanks a lot for any help!

Re: Calculate the average employee tenure in months for latest role

FreemanZ — Fri, 07 Apr 2023 15:12:13 GMT

hi Chris2016

try to plot a measure like:

Measure = AVERAGEX( VALUES(TableName[Name]), CALCULATE(AVERAGE(TableName[_Months in latest Role])) )

it worked like:

Re: Calculate the average employee tenure in months for latest role

Chris2016 — Fri, 07 Apr 2023 15:45:23 GMT

Hi, FreemanZ ,

Thanks so much for the fast reply, the measure works!

Best regards!

topic Calculate the average employee tenure in months for latest role in DAX Commands and Tips

Calculate the average employee tenure in months for latest role

Re: Calculate the average employee tenure in months for latest role

Re: Calculate the average employee tenure in months for latest role