Employee Turnover

[Siddiq8686]

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.

[tamerj1 · Accepted solution]

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

[Siddiq8686 · Accepted solution]

This solution is working fine

[Siddiq8686]

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)

[tamerj1]

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

[tamerj1 · Accepted solution]

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

[Siddiq8686]

Hello Tamerj1,

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

Regards,

Muhammad Siddiq

[Siddiq8686]

Hello Tamerj1,

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

Regards,

Muhammad Siddiq