Rolling 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 Period / Headcount End of Fiscal Period) / 2

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

Spreadsheet ==> https://drive.google.com/drive/folders/1znTXUDMOk4XminoH17c1Jt0spPnQWodC

Tab 1 - The sample of Data

Tab 2 - The desired output.

I hope someone can help

Thanks in advance.

[v-shex-msft]

Hi @Anonymous,

I'm not so clear for your table structure to coding formula, can you please share some sample data? (your link seem not works)

Regards,

Xiaoxin Sheng

[Anonymous]

Apoligies forgot to set the data sample to share, hopefully the below link will work;

https://drive.google.com/drive/folders/14bJJqeIeLiB-jNcyWIcdbpIdDCI92PQI?usp=sharing

Thanks

[Anonymous · Accepted solution]

I've amended the link to the data and desire output. I hope the link now works.

[v-shex-msft]

Hi @Anonymous,

Please try to use following dax formulas to achieve your requirement:

Calculate table formula:

Output Summary =
SUMMARIZE (
    Data,
    Data[FiscalMonthFull],
    Data[YearMonth],
    "StartOfMonth", SUM ( Data[HeadcountStartofMonth] ),
    "EndofMonth", SUM ( Data[HeadCountEndofMonth] ),
    "Leavers", SUM ( Data[LeaverinMonth] )
)

Calculate column formula:
Monthly Turnover = DIVIDE([Leavers],([StartOfMonth]+[EndofMonth])/2,-1)

YTD Turnover =
CALCULATE (
    SUM ( 'Output Summary'[Monthly Turnover] ),
    FILTER (
        ALL ( 'Output Summary' ),
        YEAR ( [YearMonth] ) = YEAR ( EARLIER ( [YearMonth] ) )
            && [YearMonth] <= EARLIER ( [YearMonth] )
    )
)

Regards,

Xiaoxin Sheng

[Greg_Deckler]

I clicked the link and requested access.