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

    )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

adhumal2 
Did you miss out a [reporting data] column? but i see your issue.
I guess you have different average value for each row, this is because you used averagex. all the x-ending functions are iterators, they iterate over a table and evaluate an expression for each row.


Just try use ALL['Emp Data'] as the context: 

 

Measure = CALCULATE(AVERAGEX(ALL('Emp Data'),DATEDIFF([Group Entry Date],EOMONTH([Report date],0),YEAR)),KEEPFILTERS(Table2[Headcount]<>0),'Emp Data'[Group Entry Date]<>BLANK())

 

Best regards 
Paul Zheng

  • adhumal2's avatar
    adhumal2
    Helper III
    6 years ago

    Anonymous  Thanks for your reply, Yes, I missed the reporting date. The reporting date is 31/12/2020 for all rows.

    For the data which I have, the calculation in excel shows average tenure as 13.19 years. However, If i use the below formula (without ALL) then DAX gives me 14.1 years, which is almost 1 year more than the actual.