Forum Discussion

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

    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))

    )

     

adhumal2's avatar
adhumal2
Helper III
6 years ago

Anonymous amitchandak edhans Greg_Deckler 

Hi Geeks,

 

I have uploaded the original database here Original Data 

 

Problem Statement -

 

- Excel Calculation shows average tenure as 13.30, whereas the DAX calculation shows average tenure as 14.1 (For Jan 2020)

- For the similar data in Dec 2019, the average tenure was 13.1 in both Excel and DAX with same formula.

- There is sudden change in the average tenure by 1 year ( based on DAX calculations) which is not likely.

- Here is the DAX measure which I am using

 

Can you please look into this and let me know what should be the correct DAX measure?

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

adhumal2 

 

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.

 
  • adhumal2's avatar
    adhumal2
    Helper III
    6 years ago

    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))

    )