cancel
Showing results 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 III

## Average Tenure

Dear Friends,

Sorry I am posting it again as i did not get the solution yet.

I am working on an excel database to calculate the average tenure of employees.

If i do the calculation in excel the average tenure of all employees is 13.2.

However If i want to calculate average tenure using PowerBI DAX the answer is 14.2 which is not matching with excel calculation.

The correct answer should be 13.2. (as shown in yellow column in the attached file)

Herewith attached the original dataset https://drive.google.com/file/d/1qOFPLare3RpYegqYpU9uddg6ZWrnIvLP/view

Here is the formula which I am using :

1 ACCEPTED SOLUTION
Community Champion

DATEDIFF with YEAR option is rounding up any decimal values. If the year difference between two dates is 1.25 year, Excel is calculating it as 1 whereas DAX is calculating as 2.

CALCULATE(AVERAGEX(Emp),

ROUNDDOWN(DATEDIFF(Table1[Group Entry Date],Table1[Reporting Date],MONTH)/12,0))

)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

2 REPLIES 2
Community Champion

DATEDIFF with YEAR option is rounding up any decimal values. If the year difference between two dates is 1.25 year, Excel is calculating it as 1 whereas DAX is calculating as 2.

CALCULATE(AVERAGEX(Emp),

ROUNDDOWN(DATEDIFF(Table1[Group Entry Date],Table1[Reporting Date],MONTH)/12,0))

)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Helper III

@vivran22This saved my day 😊. I was struggling with this since last 2-3 days and was tired of explaining it to everyone to find solution. You deserve the Kudos!

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.