Forum Discussion
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, what was the tenure on that date?
I already have current tenure based on today, but we need to see tenure over time.
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.
6 Replies
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/Here is a sample:
Date Chosen: 5/1/2018 EecEEID Last Hire Date Termination Date Tenure as of Date Chosen 1 06/02/2007 10.92 2 07/27/2019 #NUM! 3 08/03/2018 #NUM! 4 11/11/2018 #NUM! 5 11/16/2000 17.47 6 04/12/2005 13.06 7 03/17/1994 2/27/2017 22.97 Date Chosen: 2/11/2017 EecEEID Last Hire Date Termination Date Tenure as of Date Chosen 1 06/02/2007 9.70 2 07/27/2019 #NUM! 3 08/03/2018 #NUM! 4 11/11/2018 #NUM! 5 11/16/2000 16.25 6 04/12/2005 11.84 7 03/17/1994 2/27/2017 22.92 Date Chosen: 3/25/2022 EecEEID Last Hire Date Termination Date Tenure as of Date Chosen 1 06/02/2007 14.82 2 07/27/2019 2.66 3 08/03/2018 3.64 4 11/11/2018 3.37 5 11/16/2000 21.37 6 04/12/2005 16.96 7 03/17/1994 2/27/2017 22.97 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.Thank you! This worked!
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.
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.
