Need to find attrition rate for HR data

[Hanuma_Srikiran]

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

[Anonymous]

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

[Eric_Zhang]

---

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

[Hanuma_Srikiran]

Thanks .

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

[Phil_Seamark]

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.

[Hanuma_Srikiran]

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