topic Re: Employee Turnover in DAX Commands and Tips

Employee Turnover

Siddiq8686 — Sat, 12 Feb 2022 05:42:06 GMT

Hello Everyone,

I am attaching a table below showing month wise employee population and separations (employees who left the organization.

MonthQTRPopulationLeaversTurnover

Jan	QTR 1	2830	331
Feb	QTR 1	4475	266
March	QTR 1	4428	291
April	QTR 2	3150	442
May	QTR 2	3127	308
June	QTR 2	4852	310
July	QTR 3	2438	316
Aug	QTR 3	2779	357
Sept	QTR 3	4725	295
Oct	QTR 4	3527	338
Nov	QTR 4	4756	464
Dec	QTR 4	4835	320

I need to make a DAX measure to calculate the employee turnover as per below formula.

For montly turnover:

No of Separation for respective month / employee population of 1st month of respective quarter

for exmaple is i need to calculate the turnover for the month of June then i need to divide the separations of june by the employee population of April, as april is the first month of quarter 2.

The same formula will be used if i need to calculate the turnover of employee from jan to June then i will divide the sum of separation from jan to june by the avergae population of Jan plus April.

Can any one help me pleaase.

Re: Employee Turnover

Siddiq8686 — Sat, 12 Feb 2022 06:08:08 GMT

and if i need to calculate the turnover of employee for a particular year then below calculation needs to be done;

Total separation of the years / (( employees population of Jan + employees population of April+employees population of July+ employees population of Oct)/4)

Re: Employee Turnover

tamerj1 — Sat, 12 Feb 2022 10:25:09 GMT

Hi Siddiq8686

Please find sample file with solution here https://www.dropbox.com/t/Ojbaa8cK7U2b6goZ

Your report looks like this

First you need to have two relationships between the Date table and your table, one at "Year Month" granularity and the other at "Year Quarter" granularity

Then use this code to deactivate the non-required relationship and activate the required one. Also something needed to be done at the grand total level (which is the calculation you need for multiquarter selection)

Montly Turnover = VAR Leavers = SUM ( 'Table'[LeaversTurnover] ) VAR MonthlyPopulation = CALCULATE ( AVERAGE ( 'Table'[Population 1st Month of QTR] ), CROSSFILTER ( 'Date'[Year Month], 'Table'[Year Month], NONE ), USERELATIONSHIP ( 'Date'[Year Quarter], 'Table'[Year Quarter] ) ) VAR TotalPoulation = SUM ( 'Table'[Population 1st Month of QTR] ) VAR Result = IF ( HASONEVALUE ( 'Date'[Year Month] ), DIVIDE ( Leavers, MonthlyPopulation ), DIVIDE ( Leavers, TotalPoulation ) ) RETURN Result

Please let me know if I solved your problem. If so kindly mark this reply as accepted solution. Thank you

Re: Employee Turnover

tamerj1 — Mon, 14 Feb 2022 08:18:02 GMT

Siddiq8686
The final report looks like this

Make sure you adjust relations like this

Following are the measures' code:

Employee Left = SUM ( Separation_Data4[FTE] )

Population = VAR MonthlyPopulation = SUM ( Payroll_Merge_File[FTE] ) VAR QuarterAveragePopulation = CALCULATE ( AVERAGE ( Payroll_Merge_File[Quarterly Population] ), ALLSELECTED (Calendar_Table ), CROSSFILTER ( Calendar_Table[Date], Payroll_Merge_File[Payroll Proces Date], None ), USERELATIONSHIP (Calendar_Table[Year Quarter], Payroll_Merge_File[Year Quarter] ) ) VAR Result = IF ( HASONEVALUE ( Calendar_Table[Year Month] ), MonthlyPopulation , QuarterAveragePopulation ) RETURN Result

Monthly Turnover = DIVIDE ( [Employee Left], [Population] )

Re: Employee Turnover

Siddiq8686 — Mon, 14 Feb 2022 08:26:41 GMT

Hello Tamerj1,

Thanks for the solution. Now the measure is working perfect. Many Thanks once again.

Regards,

Muhammad Siddiq

Re: Employee Turnover

Siddiq8686 — Mon, 14 Feb 2022 08:26:46 GMT

Hello Tamerj1,

Thanks for the solution. Now the measure is working perfect. Many Thanks once again.

Regards,

Muhammad Siddiq

Re: Employee Turnover

Siddiq8686 — Mon, 14 Feb 2022 08:28:02 GMT

This solution is working fine