Forum Discussion
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 '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
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)))
13 Replies
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.
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-trend/ba-p/882970Appreciate your Kudos.
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
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.
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]edhans Greg_Deckler amitchandak - I appreciate your responses.
I have herewith provided the link to the sample data Sample Data
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 ZhengAnonymous 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.
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?
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)))
