Calculate Service for Distinct Techs

[ConnieMaldonado]

Hello - I'm trying to calculate the average service for technicians trained by specific trainers.  My data looks like this:

Date	Tech Name	Tech Hire Date	Tech Employee Number	Trainer EE Number	Trainer Name	Office	Training Classroom Hours	Training Ride Along Hours	Total Training Hours
1/1/2021	John Doe	5/15/2018	103665	148780	Trainer 1	Raleigh	7		7
1/2/2021	John Doe	5/15/2018	103665	148780	Trainer 1	Raleigh	6		6
1/3/2021	John Doe	5/15/2018	103665	148780	Trainer 1	Raleigh	4	4	8
1/4/2021	John Doe	5/15/2018	103665	148780	Trainer 1	Raleigh	8		8
1/1/2021	Joe Smith	4/3/2019	12345	148780	Trainer 1	Raleigh	5		5
1/2/2021	Joe Smith	4/3/2019	12345	148780	Trainer 1	Raleigh	3		3
1/3/2021	Joe Smith	4/3/2019	12345	148780	Trainer 1	Raleigh	7	2	9
1/4/2021	Joe Smith	4/3/2019	12345	148780	Trainer 1	Raleigh	5		5
1/1/2021	Larry Hall	10/1/2020	345678	556677	Trainer 2	Atlanta	7		7
1/5/2021	Larry Hall	10/1/2020	345678	556677	Trainer 2	Atlanta	6		6
1/7/2021	Larry Hall	10/1/2020	345678	556677	Trainer 2	Atlanta	4	2	6
1/1/2021	Joe Jones	1/1/2020	678903	556677	Trainer 2	Atlanta	5		5
1/5/2021	Joe Jones	1/1/2020	678903	556677	Trainer 2	Atlanta	3		3
1/7/2021	Joe Jones	1/1/2020	678903	556677	Trainer 2	Atlanta	1	4	5

I can calcuate service as follows:

Service Months = DIVIDE (Today() - Tech Hire Date, 30)

To calculate the average for each trainer (of their respective trainees), I can sum the service months and divide by the number of distinct techs.  My issue is that since I have the same technicians for for multiple dates, the service is counted multiple times.  I need to sum the service months for distinct techs.  Is there a way to do that?

Here's the output I want:

Trainer Name	Trainer EE Number	Number of Trainees (Technicians)	Average length of service of technicians (trainees) in MONTHS
Trainer 1	148780	2	54
Trainer 2	556677	2	16

Any help would be appreciated.  Thanks!

[amitchandak · Accepted solution]

@ConnieMaldonado , You can have measure like this

sumx(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

You can have

Averagex(summarize(Table, Table[Tech Name],[Tech Hire Date]), datediff([Tech Hire Date], today(),DAY)/30)

or use min of hire date

sumx(summarize(Table, Table[Tech Name], "_TechHireDate",Min([Tech Hire Date])), datediff([_TechHireDate], today(),DAY)/30)

[ConnieMaldonado]

Awesome!  Thank you.