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

    )

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Sample data and expected output is the quickest way to get to an answer. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Perhaps something like:

Avg Tenure Measure =
  VAR __Table = FILTER('Emp Data',[Headcount]<>0 && [Group Entry Date]<>BLANK())
  VAR __Taable1 =
    ADDCOLUMNS(
      __Table,
      "__Years",DATEDIFF([Group Entry Date],EOMONTH([Reporting Date],0),YEAR) + 1,
    )
RETURN
  AVERAGEX(__Table1,[__Years]