Calculate the average employee tenure in months for latest role

[Chris2016]

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!

[FreemanZ · Accepted solution]

hi @Chris2016 

try to plot a measure like:

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

it worked like:

[Chris2016]

Hi, @FreemanZ ,

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

Best regards!