Employee Turnover

[MuhammadSiddiq]

Hello,

I am needing help in clculating employee turnover to show monthly trend. The formula used to calculate turnover is like this, suppose if we want to calculate the turnover for the month of January then following formula can be used; 

Turnover for Janauary  = Employee left in Janauary /  Popoulation of janauary

Turnover for February  = Employee left in february /  Popoulation of janauary

Turnover for March      = Employee left in March /  Popoulation of janauary

Turnover for April         = Employee left in April /  Popoulation of April

Turnover for May          = Employee left in May /  Popoulation of April

Turnover for June          = Employee left in June /  Popoulation of April

Turnover for July           = Employee left in July /  Popoulation of July

Turnover for August      = Employee left in August /  Popoulation of July

and so on.....

If need the overall turnover for the whole year the we need to calculate by using following formula;

Turnover for year = Total number of Leaver during the year / Average (Population of Jan+ Population of April+population of July+ population of October)

I am unable to calculate this by using DAX, Can anybody help me out to calculate this..

Looking forward for solution

Thanks,

Muhammad Siddiq

[Greg_Deckler]

This looks like an interesting problem. Can you provide some example data and expected output for this? This would really help understand you problem.

[MuhammadSiddiq]

Hey Smoupre,

Thanks for replying and  i tried alot but failed to get the output am pasting a sample data for your reference.

Month	Active Employees	Separated Employees	Turnover Formula	Turnover %
Jan	100	10	10/100	10%
Feb	105	5	5/100	5%
March	106	3	3/100	3%
April	109	2	2/106	2%
May	112	15	15/106	14%
June	115	20	20/106	18%
July	118	5	5/118	4%
Aug	120	15	15/118	13%
Sept	121	25	25/118	21%
Oct	125	21	21/125	17%
Nov	130	18	18/125	14%
Dec	150	9	9/125	7%
Yearly Turn Over	(jan+April+July+Oct)/4	148	148/(100+109+118+125)/4	8%

This is just to give you an idea. Looking forward for the solution, as my presentation is due bby next week and i got stuck with this dynamic formula.

Regards,

Muhammad Siddiq

[v-yuezhe-msft]

@MuhammadSiddiq,

Create the following columns in your table.

Quarter = IF(OR(OR(Table[Month] = "Jan",Table[Month] = "Feb"),Table[Month] = "March"),1,IF(OR(OR(Table[Month] ="April",Table[Month] ="May"),Table[Month] ="June"),2,IF(OR(OR(Table[Month] = "July",Table[Month] ="Aug"),Table[Month] ="Sept"),3,4)))

MinEmployee = CALCULATE(MIN(Table[Active Employees]),FILTER(Table,Table[Quarter]=EARLIER(Table[Quarter])))

Turnover = Table[Separated Employees]/Table[MinEmployee]

Then create the following measures in your table.

sum employees = SUM(Table[Separated Employees])

summinemployee = SUM(Table[MinEmployee])/3

Year Turn over = DIVIDE( [sum employees]/[summinemployee],MAX(Table[Quarter]))

Regards,
Lydia

[MuhammadSiddiq]

Hey Lydia,

Thanks for the solution its works. But whan will be solution if the data is available in two seperate table one is of ACTIVE EMPLOYEE and the other one is of SEPARATED EMPOYEE and both are uploaded to data model then how we can calculate the same turnover by using the same formula. Below are the sample table for you reference.

Name          Month          Status

Alex	Jan	Active
Maria	Feb	Active
Maria	Feb	Active
Maria	Feb	Active
Sid	Feb	Active
Zahid	March	Active
Zoe	March	Active
Zeus	March	Active
Kahisf	April	Active
Kahisf	April	Active
Carol	April	Active
Martin	May	Active
Wick	May	Active
Ricku\y	May	Active
Rex	June	Active
Ted	June	Active
Juliana	Juy	Active
Julia	Juy	Active
Arman	August	Active
Arman	August	Active
Nancy	Sept	Active
Matthew	Sept	Active
Bela	Oct	Active
Betta	November	Active
Oliver	November	Active

Name              Month            Status

Siddiq	Jan	Separated
Sahid	Feb	Separated
John	Feb	Separated
Johny	March	Separated
Terry	April	Separated
Martin	May	Separated
Luther	June	Separated
Katherine	Juy	Separated
Williams	August	Separated
Bony	Sept	Separated
Bretta	Oct	Separated
Carla	November	Separated

Looking for a solution from you for making it dynamic.

Regards,

Muhammad Siddiq

[Greg_Deckler]

Can you just use an Append query to append the two tables together or is there a reason you need them in two separate tables?

[MuhammadSiddiq]

Hey Smoupre,

I can append both tables together also there is no special reason to use both tables separate. The only reason for using both tables separated is to build relationship and to make the formula more dynamic and functioning. The calculated measure can work for both Month slicer and year slicer also.

Looking forward some magic from you guys.

Regards,

Muhammad Siddiq

[v-yuezhe-msft]

@MuhammadSiddiq,

Please help to post expected result based on the new sample tables you shared.

Regards,
Lydia

[MuhammadSiddiq]

okay i am attaching shortly

[MuhammadSiddiq]

Hey Lydia,

Below is the expected result from the attached 2 table. The approach and formula will same as you calculated in first example i.e 8%.

But now 2 separated tables are uploaded to data models and are lonked with the dimension table and we need to calculate the same turnover using the previous approach and formula. The expected results are ;

Month    -             Turnover%

Jan	33%
Feb	67%
March	33%
April	33%
May	33%
June	33%
July	50%
August	50%
Sept	50%
Oct	100%
November	100%
December	100%
Yearly Turnover	578%

we need to calculate the monthly turnover as well as yearly from the data table using sums and averages but with the same previous approach.

Regards,

Muhammad Siddiq

[MuhammadSiddiq]

any luck on the above solution?

[MuhammadSiddiq]

I can also share sample excel file for best understanding.