Calculating Employee Tenure

[ejafarov]

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])

    )

[ejafarov]

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 

[wdx223_Daniel]

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)