Calculating Employee Tenure

ejafarov — Tue, 28 Feb 2023 16:26:32 GMT

Hi there,

I have the below data.

Based on the information I provided I want to calculate employee tenure based on the date filter in my calendar table. However, the formula I wrote is not returning the expected results. ou please help me.

When I make a filter for example December 2022 the data does not return me tenure until December 2022. If

Could you please help me?

Data set:

ID	Status	Start Date	Resignation date
30	Resigned	03/09/2012	31/01/2023
1461	Resigned	02/10/2020	31/01/2023
1718	Resigned	26/07/2022	26/01/2023
1200	Resigned	20/12/2018	25/01/2023
1716	Resigned	21/07/2022	23/01/2023
1679	Resigned	19/04/2022	19/01/2023
1788	Active	05/01/2023
1789	Active	06/01/2023
1790	Active	06/01/2023
1791	Active	23/01/2023
1792	Active	16/01/2023
1793	Active	24/01/2023
1794	Active	25/01/2023
1795	Active	24/01/2023
1796	Active	24/01/2023
1797	Active	24/01/2023
1798	Active	24/01/2023

And my dax:

Employee Tenure final =

VAR FilterDate = MAX(Calendar[Date])

VAR StartDate = MIN('Pİ'[Start Date])

VAR ResignationDate = MIN('Pİ'[Resignation date])

VAR Tenure =

IF(ISBLANK(ResignationDate),

DATEDIFF(StartDate, FilterDate, MONTH),

IF(ResignationDate > FilterDate,

DATEDIFF(StartDate, FilterDate, MONTH),

DATEDIFF(StartDate, ResignationDate, MONTH)

)

RETURN

CALCULATE(

Tenure,

USERELATIONSHIP('Pİ'[Start Date], 'Calendar'[Date]),

USERELATIONSHIP('Pİ'[Resignation date], 'Calendar'[Date])

)

Re: Calculating Employee Tenure

wdx223_Daniel — Wed, 01 Mar 2023 07:58:31 GMT

suggest to cut the relationship between these two tables and try this measure

=DATEDIFF(MAX('Pİ'[Start Date]),MIN(MAX(Calendar[Date]),COALESCE(MIN('Pİ'[Resignation date]),MAX(Calendar[Date]))),MONTH)

Re: Calculating Employee Tenure

ejafarov — Wed, 01 Mar 2023 10:41:32 GMT

Hello,

Thank you, it works. but when I cut the relationship between those tables, it affects other calculations (for ex active employee count).
How can i solve it ?

Thank you

topic Re: Calculating Employee Tenure in DAX Commands and Tips

Calculating Employee Tenure

Re: Calculating Employee Tenure

Re: Calculating Employee Tenure