Average Tenure

[adhumal2]

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 :

[vivran22 · Accepted solution]

Hello @adhumal2 ,

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.

You may adjust your formula by :

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/

Connect on LinkedIn

[adhumal2]

@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!