AVERAGE TENURE using CALCULATE FUNCTION

[adhumal2]

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 'employee group entry date' and 'reporting date' (e.g. 31st Jan, 2020) in order to get the average tenure of all employees

2 - The table needs to be filtered for employee status "status1" , "Status2" (i.e. employees with status other than these should not be considered for calculation

3. The table needs to be filtered for employee type "type1" , "type2" (i.e. employees with types other than these should not be considered for calculation.

4. The calculations should exclude employees with headcount status 0

Can someone help me with the DAX measure for above calculation?

I tried using below formula (since the 'emp data' table was already filtered for above mentioned employee type and employee status. But the formula gives me results which are different than the actual ones

[adhumal2]

@V-pazhen-msft @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?

[V-pazhen-msft]

@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 · Accepted solution]

@V-pazhen-msftMany 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]

@edhans @Greg_Deckler @amitchandak - I appreciate your responses.

I have herewith provided the link to the sample data Sample Data

[V-pazhen-msft]

@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]

@V-pazhen-msft  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.

[Greg_Deckler]

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]

[adhumal2]

Here is the screenshot of the sample data

1. The final calculation should calculate 

 - Average tenure for employees with status 'Active' ,'Paid Leave' , 'Unpaid Leave'

- While doing above calculation , it should exclude employees with type "Blue Collar" with above status

- While doing above calculation , it should exclude employees with Headcount =0

Here is the input

Here is the table for which i need to calculate the average tenure

[edhans]

@adhumal2 we are trying to help. Please don't provide screenshots of data we have to key in. I provided a link to an excellent post on how to share data so we can copy|paste, or better yet just open an Excel or PBIX file. It is also very helpful to have a screenshot or table of the expected output so when we are testing measures, it delivers the expected output.

[Greg_Deckler]

@edhans - That is a fantastic post by @ImkeF , I just added that post as a link in my blog article where I talk about posting data.

[edhans]

Yup @Greg_Deckler - she just put that together over the last few days and mentined it in the Teams channel. I wish your post and her post could be made Stickies in the forums. They would get end users answers so much faster.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.
In between refer my blog on HR that deal with how to find current employees :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[edhans]

Can you please share some data (fake or otherwise, as long as it isn't confidential) via OneDrive, or follow guidelines here? I am not sure I fully grasp your issue and I don't want to spend half an hour keying in fake data thinking I am emulating your model and end up wasting my time because I misunderstood.