Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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




Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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




    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous - 

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.