topic Re: How Can I Calculate the Years Service of each employee in DAX Commands and Tips

How Can I Calculate the Years Service of each employee

amal_01 — Mon, 14 Apr 2025 18:33:55 GMT

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

Re: How Can I Calculate the Years Service of each employee

Greg_Deckler — Mon, 14 Apr 2025 18:40:06 GMT

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.

Re: How Can I Calculate the Years Service of each employee

amal_01 — Tue, 15 Apr 2025 06:39:36 GMT

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

Re: How Can I Calculate the Years Service of each employee

Greg_Deckler — Tue, 15 Apr 2025 21:07:38 GMT

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.

Re: How Can I Calculate the Years Service of each employee

v-aatheeque — Sat, 19 Apr 2025 18:23:37 GMT

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!

Re: How Can I Calculate the Years Service of each employee

DAXian — Sat, 19 Apr 2025 19:13:39 GMT

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. 🙂

Re: How Can I Calculate the Years Service of each employee

v-aatheeque — Tue, 29 Apr 2025 07:15:33 GMT

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!

Re: How Can I Calculate the Years Service of each employee

v-aatheeque — Sat, 03 May 2025 16:58:57 GMT

Hi amal_01

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

Thank you!