Employee Turnover by Fiscal Year

[Anonymous]

Hi all,

I'm trying to create a power bi report that monitors the organisations employee turnover rate. The Turnover calculation I'm using is below.

No of Leavers Divided by

(Headcount Start of Fiscal Year / Headcount End(or latest Period) of Fiscal Year) / 2

I've enclosed a spreadsheet with 2 tabs that I hope will explain what I am trying to achieve. Hopefully it makes sense.

Spreadsheet containing the Data table==> https://drive.google.com/drive/folders/14bJJqeIeLiB-jNcyWIcdbpIdDCI92PQI?usp=sharing

Tab 1 - The sample of Data

Tab 2 - The desired output.

I hope someone can help

Thanks in advance.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Please check the following steps as below.

1. Create a date table and create two calculated column in it. Then create relationship between it and the fact table.

Table = CALENDARAUTO()

YearMonth = YEAR('Table'[Date])*100+MONTH('Table'[Date])

Year = YEAR('Table'[Date])

2. Create the measures as below.

Monthly Turnover = 
VAR SOM =
    CALCULATE ( SUM ( Data[HeadcountStartofMonth] ) )
VAR EOM =
    CALCULATE ( SUM ( Data[HeadCountEndofMonth] ) )
VAR Leav =
    CALCULATE ( SUM ( Data[LeaverinMonth] ) )
VAR result = Leav
    / ( ( SOM + EOM )
    / 2 )
RETURN
    IF ( ISBLANK ( result ), BLANK (), result )

Rolling YTD Turnover = 
IF (
    ISBLANK ( [Monthly Turnover] ),
    BLANK (),
    CALCULATE (
        SUMX (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[YearMonth] <= MAX ( 'Table'[YearMonth] ) ),
            [Monthly Turnover]
        ),
        VALUES ( 'Table'[Year] )
    )
)

3. Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Thank you very much - I tweaked the formula to account for Fiscal year - but it works well.

Thanks Again