Forum Discussion
Employee Average Tenure not working
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.
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
9 Replies
davehusMemorable Member
Hi Anonymous , Try this and see if it's what you need.
https://drive.google.com/file/d/1nD4H44A4Iw-uewnoCcNvVEJ6Pl57MZBt/view?usp=sharing
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=FFFMWbI 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
Hi Anonymous,
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
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=FFFMWbHi Anonymous 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.