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.
Siddiq8686
Helper I
4 years agoAnonymous 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