Forum Discussion

Siddiq8686's avatar
Siddiq8686
Icon for Helper I rankHelper I
4 years ago
Solved

Staff 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...
  • tamerj1's avatar
    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 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.