Forum Discussion
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 :
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
2 Replies
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
