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
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
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.
- Siddiq86864 years ago
Helper I
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