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.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi jfoulk 

 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/