Forum Discussion
Staff Turnover - Need Help
- 4 years ago
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 isQuarterly 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 ResultEmployee 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 ResultTotal Population = [Hires RT] - [Employee Left RT]Your report looks like this
Please let me know if you still have any doupt.
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
Anonymous
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.