How Can I Calculate the Years Service of each employee

[amal_01]

I have Table EmploymentDetails I will mention the importent columns 

EmploymentStartDate , PersonnalNumber 
I want to calculate the Years of Service and the number of months in Years of Service

as You shown above I tired to create column to calculate Years of Service 
"

Years of Service =

VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY(), YEAR)

VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY() , MONTH) - (Years * 12)

VAR result = Years + (Months / 12)

RETURN

ROUND(result, 1)
"
this Measure worked correctly it has calculated the Years of Service from EmploymentStartDate of each employee untily today 
but I want to calculate the Years of Service depend on the slicer
the employee's years of service are calculated based on the date

I tried to create this Column
"

YearsOfServices =

VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), YEAR)

VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), MONTH) - (Years * 12)

VAR result = Years + (Months / 12)

RETURN

ROUND(result, 1)
"
but there is not result 
could anyone help me to create a column and Measure please ?
thank in advance 

[DAXian · Accepted solution]

Hi @amal_01 

Try using this dax formula : 

YearsOfServices =

VAR Years = DATEDIFF( SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]),CALCULATE(MAX(EmploymentDetails[EmploymentStartDate]),ALL(EmploymentDetails)), YEAR)

VAR Months = DATEDIFF(SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]),CALCULATE(MAX(EmploymentDetails[EmploymentStartDate]),ALL(EmploymentDetails)), MONTH) - (Years * 12)

VAR result = Years + (Months / 12)

RETURN

ROUND(result, 1)

As a bonus, after adding the above formula, create a new measure and put this : 

AvgYearsOfServices =

AVERAGEX(

    KEEPFILTERS(VALUES('EmploymentDetails'[PersonnelNumber])),

    CALCULATE([YearsOfServices])

)

Let us know if it works. 🙂

[v-aatheeque]

Hi @amal_01 ,

If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @amal_01 

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[Greg_Deckler]

@amal_01 If I understand, you can just use MAX('Dates'[Date]) in place of TODAY(). Basically, get the last date selected by your slicer.

[amal_01]

Hi @Greg_Deckler 
thanks for response ,
but i Used MAX('EmploymentDetails'[EmploymentStartDate]) Does not work correctly ,
the employees are filtered by the slicer depande on thire EmploymentStartDate
 but the Years of Service are not calculated based on slicer 
For Exp: 
as you shown here 

the employee 12564 his EmploymentStartDate on 12/6/2020 and slicer is on 5/19/2022 so it sould be his Years of Service 1 year and 3 month not 4 Years

[Greg_Deckler]

@amal_01 OK, first question is what is the Slicer based on? Is it a Dates table or is it some date column in the EmployementDetails table? If it is a Dates table, is that Dates table related to the EmploymentDetails table? It's impossible to answer the question reliably without some basic details.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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.

[v-aatheeque]

Hi @amal_01 

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!