Staff Turnover - Need Help

[Siddiq8686]

Hello Everyone,

I need help in turnover measure. I have already made that and its working fine over month and years. but when i needs to calculate the overall turnover for all years it gives wrong answers. Someone also helped me in this regards but still calculation is giving wrong answer if i select all years. Let me give details about my data model.

i have three table

1- Monthly appended payroll  table consisting of employee numbers, Their hire dates, payroll process date,employee FTE workloads and other employee related information.

2- A table containing all separated employees consisisting of their employe numbers, their separation(end) date and their FTE workloads.

3- Calendar Tabake consisting of consective dates, month, quarters, year and other calendar table parameters.

I am attaching a sample aggregated information of all table for a better clarity:

The below table is showing monthly Employee count and Montly number of empoyee left the organization. The second table show how to calculate turnover for each month, quarters and years. The turnover formula is a bit complex.

Date	Employee Count	Employee Left
1/1/2016	2279	10
2/1/2016	2258	11
3/1/2016	2329	5
4/1/2016	2337	13
5/1/2016	2503	10
6/1/2016	2597	13
7/1/2016	2615	15
8/1/2016	2646	14
9/1/2016	2682	17
10/1/2016	2694	10
11/1/2016	2706	4
12/1/2016	2719	8
1/1/2017	2716	21
2/1/2017	2708	21
3/1/2017	2673	19
4/1/2017	2623	4
5/1/2017	2616	17
6/1/2017	2608	9
7/1/2017	2686	13
8/1/2017	2746	7
9/1/2017	2773	1
10/1/2017	2780	2
11/1/2017	2793	20
12/1/2017	2812	15

The below table show monthly calculation of turnover:

Please note the for every quater we will take the population of first month of every quarter as denominators. Like for Jan turnover we will take  jan employe count as denominator  and same for Feb and March. 

Date	Employee Count	Employee Left	Monthly Turnover	Monthly Turnover Formula
1/1/2016	2279	10	0.44%	10/2279
2/1/2016	2279	11	0.48%	11/2279
3/1/2016	2279	5	0.22%	5/2279
4/1/2016	2337	13	0.56%	13/2337
5/1/2016	2337	10	0.43%
6/1/2016	2337	13	0.56%
7/1/2016	2615	15	0.57%
8/1/2016	2615	14	0.54%
9/1/2016	2615	17	0.65%
10/1/2016	2694	10	0.37%
11/1/2016	2694	4	0.15%
12/1/2016	2694	8	0.30%
1/1/2017	2716	21	0.77%
2/1/2017	2716	21	0.77%
3/1/2017	2716	19	0.70%
4/1/2017	2623	4	0.15%
5/1/2017	2623	17	0.65%
6/1/2017	2623	9	0.34%
7/1/2017	2686	13	0.48%
8/1/2017	2686	7	0.26%
9/1/2017	2686	1	0.04%
10/1/2017	2780	2	0.07%
11/1/2017	2780	20	0.72%
12/1/2017	2780	15	0.54%

For 2016 turnover we can calculate the turnover as per below given formula:

(sum of employee left in 2016) / Average of population of ( Jan+ April + Jul + Oct)

and same formula will be used for each year.

but if want to calculate the accumulated turnover for year 2016 and 2017, below formula will be used.

(sum of employee left in 2016 and 2017)  / Average of population of ( Jan 2016+ April 2016 + Jul  2016+ Oct 2016) + 

                                                                     Average of population of ( Jan 2017+ April 2017 + Jul  2017+ Oct 2017)

and same formula will be used if multiples years needs to be calculated.

Looing forward for a solution.

Regards,

Sid

[Siddiq8686]

@tamerj1 ,

Thanks for your time and response,

Let me check the calculations i will inform you if any help is required.

Thanks,

Sid

[v-yiruan-msft]

Hi @Siddiq8686 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Any comment or problem, please feel free to let us know.

Best Regards

[tamerj1 · Accepted solution]

Hi @Siddiq8686 
Here is the file with the solution https://www.dropbox.com/t/VXuA2561R4sF1Pul
The data model looks like this

The flag calculated column in the Merge_File3 table is

Quarterly Population = 
IF ( 
    MONTH ( Merge_File3[Payroll Process Date] ) IN { 1, 4, 7, 10 },
    TRUE 
)

The measures are

Average Population = 
VAR MonthlyPopulation =
    SUM ( Merge_File3[FTE] )
VAR QuarterAveragePopulation =
    AVERAGEX ( 
        VALUES ('Calendar Table'[Year Quarter] ),
        CALCULATE ( 
            SUM ( Merge_File3[FTE] ),
            Merge_File3[Quarterly Population] = TRUE
        )
    )
VAR Result = 
    IF (
        HASONEVALUE ( 'Calendar Table'[Year Month] ),
        MonthlyPopulation ,
        QuarterAveragePopulation 
    )
RETURN
    Result

Employee Left = COUNTROWS ( Separation_Data4 )

Employee Left RT = 
VAR LastMonthInFilter =
    MAX ('Calendar Table'[Year Month Number] )
RETURN
    CALCULATE ( 
        [Employee Left],
        REMOVEFILTERS ('Calendar Table' ),
        'Calendar Table'[Year Month Number] <= LastMonthInFilter
    )

% Turnover = 
DIVIDE (
    [Employee Left],
    SUMX ( 
        VALUES ('Calendar Table'[Year] ),
        [Average Population]
    )
)

New Hires = COUNTROWS ( Hireing_Data )

Hires RT = 
VAR LastMonthInFilter = 
    MAX ( 'Calendar Table'[Year Month Number] )
VAR Result =
    CALCULATE ( 
        [New Hires],
        REMOVEFILTERS ( 'Calendar Table' ),
        'Calendar Table'[Year Month Number] <= LastMonthInFilter
    )
RETURN
    Result

Total Population = [Hires RT] - [Employee Left RT]

Your report looks like this

Please let me know if you still have any doupt.

[Siddiq8686]

Hey Tamerj,

Thanks for your solution. This is working fine now.

Apologies for delayed response i was out of for some personal work.

Thanks again.

Regards,

Sid

[Siddiq8686]

@v-yiruan-msft @amitchandak 

I am sharing my Pbix for better understanding.

https://www.dropbox.com/s/skr5de4x53kkrcd/Employee%20Sample%20Data.pbix?dl=0

Looking for sa solution.

Thanks and Cheers,

Sid

[Siddiq8686]

Hey Tamerj,

The measure is workin file over year slicer but when i needs to slice the data over employees Division, Employe Gender and Ethinicty wise. result is constant for all slicers. For example, if i used gender in slicer, and when i select FEMALE from gender then formuls should take Sepatration of Female and denominators of female popultion. but right now the formula is taking overall population in denominator for all slicer. I think this is because we are taking employe population from quarter year colums and in that column we have a constant value of monthly population.

Can i make it dynamic.

Regards,

Sid

[v-yiruan-msft]

Hi @Siddiq8686 ,

You can refer the following links to get the staff turnover:

Staff Turnover Calculation In Power BI Using DAX – HR Insights

Calculating Turnover Rate in DAX ( But: Using Average Employed Per Day )

Leavers =
VAR MaxDate = Max ( Date_Table[Date] )
VAR MinDate = Min ( Date_Table[Date] )
RETURN
0 +
CALCULATE (
COUNTROWS(Raw_Data),
Raw_Data[To] <= MaxDate,
Raw_Data[To] >= MinDate,
All(Date_Table)
)
 
Headcount =
VAR MaxDate = Max ( Date_Table[Date] )
VAR MinDate = Min ( Date_Table[Date] )
RETURN
0 +
CALCULATE (
COUNTROWS(Raw_Data),
Raw_Data[From] <= MaxDate,
Raw_Data[To] >= MinDate || ISBLANK(Raw_Data[To]),
All(Date_Table)
)
 
Av_Turnver =
VAR MaxDate = Max(Date_Table[Date])
VAR MinDate = Min(Date_Table[Date])
VAR DayCount = 1 + (MaxDate - MinDate)
RETURN
DIVIDE (
[Leavers] * DayCount,
SUMX(Date_Table, [Headcount]),
0
)

Calculating Employee Turnover in DAX- In and Out

If the above ones can't help you, you can refer the following thread to upload your pbix file in your post.

How to upload PBI in Community

Best Regards

[Siddiq8686]

@v-yiruan-msft 

Thanks for your reply. I tried this but this is not helping me out. for denominator i need to take average of first month of all quarters means (  population of Jan+April+Jul+Oct)/4.

I am going to share my pbix file shortly.

[tamerj1]

Hi @Siddiq8686 
I think now is ok. Please chec and confirm https://www.dropbox.com/t/voAOBiOMvkXI5J2p

Average Population1 = 
VAR MonthlyPopulation =
    SUM ( Payroll_Merge_File[FTE] )
VAR QuarterAveragePopulation =
    AVERAGEX ( 
        VALUES ('Date'[Year Quarter] ),
        CALCULATE (
            AVERAGE ( Payroll_Merge_File[Quarterly Population] ),
            USERELATIONSHIP ( 'Date'[Year Quarter], Payroll_Merge_File[Year Quarter] )
        )
    )
VAR Result = 
    IF (
        HASONEVALUE ( 'Date'[Year Month] ),
        MonthlyPopulation ,
        QuarterAveragePopulation 
    )
RETURN
    Result 

 

[amitchandak]

@Siddiq8686 , for denominator, try a measure like

AverageX(values('Date'[Month Year]), calculate(firstnonblankvalue('Date'[Month Year], calculate(Average(Table[Employee Count]))), allexcept('Date'[Qtr Year])))

[Siddiq8686]

@amitchandak , i tried but its giving wrong answer and why this measure is not gettin filtered with other slicer. Can i share my Pbix file for clarity??