cancel
Showing results for
Did you mean:
Helper I

## Calculating Employee Tenure

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

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

)

2 REPLIES 2
Helper I

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

Super User

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)

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors