Calculate Employee Tenure Over Time

[jfoulk]

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. 

[jfoulk]

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

[Ashish_Mathur]

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]

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. 

[v-kkf-msft · Accepted solution]

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]

Thank you! This worked!

[VahidDM]

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/