Forum Discussion

adhumal2's avatar
adhumal2
Helper III
6 years ago
Solved

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 exc...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    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

vivran22's avatar
vivran22
Community Champion
6 years ago

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's avatar
adhumal2
Helper III
6 years ago

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!