Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adhumal2
Helper III
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 :

Capture.6PNG.PNG

 

 

 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
vivran22
Community Champion
Community Champion

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.