Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

)

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)

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors