Forum Discussion

jfoulk's avatar
jfoulk
Frequent Visitor
4 years ago
Solved

Calculate Employee Tenure Over Time

I am trying to calculate active employee tenure at any point in time based on Start Date, Term Date, or Selected Date  if not termed.    For example, if an associate was active on a selected date, ...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi jfoulk ,

     

    Please try the following measure:

     

    DateTenure as of Date Chosen = 
VAR DateChoose =
    MAX ( Dates[Date] )
RETURN
    IF (
        MAX ( 'Table'[Last Hire Date] ) > DateChoose,
        "#NUM!",
        DATEDIFF (
            MAX ( 'Table'[Last Hire Date] ),
            MIN ( COALESCE ( MAX ( 'Table'[Termination Date] ), TODAY () ), DateChoose ),
            DAY
        ) / 365
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jfoulk's avatar
jfoulk
Frequent Visitor
4 years ago

Here is a sample:

Date Chosen:5/1/2018  
EecEEIDLast Hire DateTermination DateTenure as of Date Chosen
106/02/2007 10.92
207/27/2019 #NUM!
308/03/2018 #NUM!
411/11/2018 #NUM!
511/16/2000 17.47
604/12/2005 13.06
703/17/19942/27/201722.97

 

Date Chosen:2/11/2017  
EecEEIDLast Hire DateTermination DateTenure as of Date Chosen
106/02/2007 9.70
207/27/2019 #NUM!
308/03/2018 #NUM!
411/11/2018 #NUM!
511/16/2000 16.25
604/12/2005 11.84
703/17/19942/27/201722.92

 

Date Chosen:3/25/2022  
EecEEIDLast Hire DateTermination DateTenure as of Date Chosen
106/02/2007 14.82
207/27/2019 2.66
308/03/2018 3.64
411/11/2018 3.37
511/16/2000 21.37
604/12/2005 16.96
703/17/19942/27/201722.97
  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi jfoulk ,

     

    Please try the following measure:

     

    DateTenure as of Date Chosen = 
VAR DateChoose =
    MAX ( Dates[Date] )
RETURN
    IF (
        MAX ( 'Table'[Last Hire Date] ) > DateChoose,
        "#NUM!",
        DATEDIFF (
            MAX ( 'Table'[Last Hire Date] ),
            MIN ( COALESCE ( MAX ( 'Table'[Termination Date] ), TODAY () ), DateChoose ),
            DAY
        ) / 365
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • jfoulk's avatar
      jfoulk
      Frequent Visitor
      4 years ago

      Thank you! This worked!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    In the first table, why should employee ID 7 be shown?  The select date is May 1, 2018 and the termination date wasFeb 27, 2017. 

    • jfoulk's avatar
      jfoulk
      Frequent Visitor
      4 years ago

      Hi!

       

      Thanks for replying to the post. My data is esentially an employee list that gets loaded from our HRIS system daily into Power BI. I can always see all hire dates, term dates (or blank if still active), so I wanted to be clear in what I expected when selecting different points in time. All dates are important depending on the date chosen.