Employee Average Tenure not working

[sidexcel]

Hi All,

I am trying to calculate the Average Tenure for employees over time AND trying to see if its possible to pivot this information by location, departments, status etc. 

I am struggling in two areas:

1. The HireDate and TermDate measure that I created is not showing expected result (even though it is in Excel). 

2. The measure is not working if I try to filter with location or department. 

Here is an example file that I have. 

https://1drv.ms/u/s!Am39VRFr8NngkDngv5IpAlBb70gM?e=2N3euB

[virtualdelo]

Can you post the result?

I don't understand your question.

[sidexcel]

Hi @virtualdelo here is the solution.

I basically have a employee data set with start and end dates. I want to calculate the running average tenure of the employees and be able to pivot this data by locaton, department, title etc. 

https://docs.google.com/spreadsheets/d/1UavrQEb5_nU-JTEA3QCZEFEhLnP85UnT/edit?usp=sharing&ouid=10698...

[sidexcel]

Hi @davehus 

I appreciate your followup. I think its working but not exactly the way I had hoped. 

There are a few issues (I have duplicated page 1 into page 1 v2)

The updated AverageTenurev2 measure currently does not show the 'rolling' nature of the average I was hoping to acheive. 

If you notice the updated table the aggregation is not working at the dimension level (either for location or department - in reality I have more dimensions such as race, sex, age, team etc). 

V2 of file: https://1drv.ms/u/s!Am39VRFr8NngkDpBbw_AEKdkmXUA?e=FFFMWb

[v-shex-msft]

Hi @sidexcel,

Did the above suggestion help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[davehus]

Hi @sidexcel , Try this and see if it's what you need. 

https://drive.google.com/file/d/1nD4H44A4Iw-uewnoCcNvVEJ6Pl57MZBt/view?usp=sharing

[sidexcel]

I appreciate your followup. I think its working but not exactly the way I had hoped. 

There are a few issues (I have duplicated page 1 into page 1 v2)

The updated AverageTenurev2 measure currently does not show the 'rolling' nature of the average I was hoping to acheive. 

If you notice the updated table the aggregation is not working at the dimension level (either for location or department - in reality I have more dimensions such as race, sex, age, team etc). 

V2 of file: https://1drv.ms/u/s!Am39VRFr8NngkDpBbw_AEKdkmXUA?e=FFFMWb

[virtualdelo · Accepted solution]

I working on it but i'm too slow i have to stop it.
Try this, where the StartDate table is your DataTable (i have set a reletionship one way and not both like you).
Here there are the day in for every employ. 
You have to stop the count when they go out.
If you need i'll give the pbix.
I hope i help you.

AverageTenurev2 = 

VAR _currentMonth = SELECTEDVALUE(StartDate[Month])
VAR HireDate = CALCULATE(MAX(FactTable[Start Date]), ALLEXCEPT(FactTable, FactTable[ID]))
VAR TermDate = CALCULATE(MAX(FactTable[End Date]), ALLEXCEPT(FactTable, FactTable[ID]))
VAR _EOM = EOMONTH(TODAY(),-1)
RETURN

VAR _StartDate = IF(HireDate <= _currentMonth, HireDate, blank())
VAR _EndDate = IF(TermDate=BLANK(), _EOM, IF(EOMONTH(TermDate,0)<= _currentMonth, MIN(_currentMonth, TermDate), blank()))

VAR day_in = IF(_StartDate < _currentMonth, DATEDIFF(_StartDate, _currentMonth, day), BLANK())

RETURN day_in

[sidexcel]

Hi @virtualdelo - 

Can you share the PBIX?

The solution for Canada is not averaging. Eg. Canada total should be 119 and for US it should be [(150+91+30)/3] = 90.33 days.

[virtualdelo]

https://we.tl/t-WZu0WxXgnT
here the pbix.
i create in the measure the variable of the days in and the variable of the nr of employ by month that u need for the average.

I hope it's an help.