Forum Discussion
Siddiq8686
Helper I
4 years agoStaff Turnover - Need Help
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 wron...
- 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.
tamerj1
Community Champion
4 years agoHi 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