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.

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.