cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hanuma_Srikiran
Frequent Visitor

Need to find attrition rate for HR data

 

Friends,

 

I have a dataset like this and i wanna calculate attrition rate of employees (in %). 

This should come in either a bar chart or line chart for visualization (monthly).

The attrition rate formula would be (difference between starting count of employees of a month and ending count of the same month)/ ((starting count of employees of a month) + (ending count of the same month)/2)).

 

I am not able to get a calculated column for attrition rate as such.

Please help me on the same.

 

DATE_OF_JOININGDate Of RsgnDepartmentEmployed Or ResignedGenderCurrent RatingEmployee CountPrevious RatingResigned 
12-Nov-16 AdminEmployedMale551700
20-Jan-1419-Feb-14MarketingResignedMale701701
18-Nov-15 ITEmployedMale631 0
05-Oct-0930-Jun-16MarketingResignedMale611521
13-Oct-1301-Mar-16AdminResignedFemale701591
04-Apr-07 AdminEmployedFemale631560

 

 

 

5 REPLIES 5
Microsoft Phil_Seamark
Microsoft

Re: Need to find attrition rate for HR data

Hi @Hanuma_Srikiran

 

Are you able to post what your expected results should be for this dataset?  This will help clarify your requirement.

 

Also if you could pick an example month and specifiy what you would expect to be the numbers for 'starting count of employees of a month' for that month.  I'm not sure if this is a cumulative figure or just those starting for that month.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Microsoft Eric_Zhang
Microsoft

Re: Need to find attrition rate for HR data


@Hanuma_Srikiran wrote:

 

Friends,

 

I have a dataset like this and i wanna calculate attrition rate of employees (in %). 

This should come in either a bar chart or line chart for visualization (monthly).

The attrition rate formula would be (difference between starting count of employees of a month and ending count of the same month)/ ((starting count of employees of a month) + (ending count of the same month)/2)).

 

I am not able to get a calculated column for attrition rate as such.

Please help me on the same.

 

DATE_OF_JOINING Date Of Rsgn Department Employed Or Resigned Gender Current Rating Employee Count Previous Rating Resigned 
12-Nov-16   Admin Employed Male 55 1 70 0
20-Jan-14 19-Feb-14 Marketing Resigned Male 70 1 70 1
18-Nov-15   IT Employed Male 63 1   0
05-Oct-09 30-Jun-16 Marketing Resigned Male 61 1 52 1
13-Oct-13 01-Mar-16 Admin Resigned Female 70 1 59 1
04-Apr-07   Admin Employed Female 63 1 56 0

 

 

 


@Hanuma_Srikiran

I have the same question as @Phil_Seamark, but first of all, I think you'll need to create a calculated table which contains rows of each month for each employee during their join date and rsgn date(if not, then to today).

 

Dim_Date = FILTER(CALENDAR("2007-01-01","2017-12-31"),DAY([Date])=1)
calculatedTable =
FILTER (
    CROSSJOIN ( HRTable, Dim_Date ),
    DATE ( YEAR ( HRTable[DATE_OF_JOINING] ), MONTH ( HRTable[DATE_OF_JOINING] ), 1 )
        <= Dim_Date[Date]
        && Dim_Date[Date]
            <= ( IF ( ISBLANK ( HRTable[Date Of Rsgn] ), TODAY (), HRTable[Date Of Rsgn] ) )
)

Capture.PNG

Hanuma_Srikiran
Frequent Visitor

Re: Need to find attrition rate for HR data

Thanks .

I need any  possible graph showing "attrition rate of employees" in (like 0.51%......)  for this data for demo purpose only.

 

Hanuma_Srikiran
Frequent Visitor

Re: Need to find attrition rate for HR data

Thanks For your reply.

I need like a sample graph showing the Attrition Rate of Employees by monthly or yearly.The results should be in percentage

Kiyomiolivares Helper I
Helper I

Re: Need to find attrition rate for HR data

Hi, i have a similar data.

I have first a table where i acumulate all the employees of the company (every month). Also, I have other table where I acumulate all the resignations per month.

What I need to have is a line chart where i show the cumulative rotation pero month, and when i apply a slicer (for example, If i only want to see the rotation of one of the divisions od the company), it can slice the line chart.

My formula for rotation is = Q resignation / average(Q employees month 1 + Q employees month 2)

 

thanks!!

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors