Forum Discussion
Solved
AVERAGE TENURE using CALCULATE FUNCTION
Hi Geeks, I am trying to calculate average tenure for all employees using a formula mentioned below: Points 1 - The table I am using is 'emp data' . I need to calculate difference between '...
AnonymousMany Thanks. I adjusted the formula as mentioned below and it worked
CALCULATE(AVERAGEX(Emp),
ROUNDDOWN(DATEDIFF(Table1[Group Entry Date],Table1[Reporting Date],MONTH)/12,0)))
The problem is the DATEDIFF expression. If we look at Person id 1, the dates are 2020.1.31 and 2017.10.1. In Excel, when the date difference is not reaching 3 full years it counted as 2 years. In Power Bi, you made the interval as Year so only looked at the Year figure, so the difference is 2020-2017 = 3.
This is where the 1 year difference come from. You could just -1 after the expression to get it correct.
DATEDIFF([Group Entry Date],EOMONTH([Reporting Date],0),YEAR)-1)
However, In my point of view, I would suggest you to use month as the interval then divide by 12 to get more accurate year difference:
Measure = CALCULATE(AVERAGEX('Emp Data',DATEDIFF([Group Entry Date],EOMONTH([Reporting Date],0),MONTH)/12),FILTER('Emp Data','Emp Data'[Headcount]<>0),'Emp Data'[Group Entry Date]<>0,'Emp Data'[Group Entry Date])
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AnonymousMany Thanks. I adjusted the formula as mentioned below and it worked
CALCULATE(AVERAGEX(Emp),
ROUNDDOWN(DATEDIFF(Table1[Group Entry Date],Table1[Reporting Date],MONTH)/12,0))
)