Rolling 12-Month Employee Turnover

[Andonip]

Hello, 

I am a fairly new user on the tool and have a tough challenge to crack on employee turnover. I am basically looking to build a metric that would breakdown our attrition in 12 month outlooks.

So for May 2025, it would take our average headcount per month (just total headcount of each month averaged) from June'24 to May'25, and then add up our leavers over that time and divide the 2 numbers for the %.

I have created 2 main data sources:

1. Heacount - which has each period Jan'25, Feb'25 etc
2. Terminations - File with our leavers, termination dates and then categorised by voluntary and involuntary
3. Linked the user ID and Period of both files

Is there a way to create something that would only look at our Permanenet Employees, and break this figure down by Total %, Voluntary and Involuntary %?

Please let me know if more info is needed ive tried to cover as best as possible the task. 

Regards,

[v-kpoloju-msft]

Hi @Andonip,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks @johnt75, for those valuable insights for this thread.

After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[Andonip]

Thank you @v-kpoloju-msft , @johnt75 

This is great thank you soo much for all your help! I am finding a small problem I think related to the Date table relationships. I have tried to replicate the Period to the Termination Date with Many to One, Single but I wont accept this due to duplicate values, and the Many to Many wont return the right figures. 

The Date Table I used was:

DateTable =

ADDCOLUMNS(

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "mmmm"),

    "Month Number", MONTH([Date])

)

And then created a Period Column that would 

Period = STARTOFMONTH(DateTable[Date])

Do you know where the issue might be?

Kind regards,

Antonis

[johnt75]

Link DateTable[Date] to the termination date, then it will allow a one-to-many, single direction relationship.

[Andonip]

I see that works perfectly and gets me the right figure! Though when testing to break down by similar elements like a Department, I can see it's still reporting on a total figure level. For example I am returning the correct leavers, but the avergae HC returns the entire company. Do you get the same?

[johnt75]

The Department column would need to be able to filter both headcount and termination tables. Ideally you would integrate that into the employee table, which filters both.

[johnt75 · Accepted solution]

The way I would approach this is to create a new table with employee info ( name, employee number, temp or permanent etc ) and link this to both headcount and termination tables. Delete the existing relationships before you do this.

Create a date table, marked as a date table, and link that to both termination and headcount tables from 'Date'[Date]. Make sure that there is a year month column of type date, set to display as "Mar 25". Use this in your visual.

Create measures like

Rolling 12 month termination total % =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    DATESBETWEEN ( 'Date'[Year month], EOMONTH ( MaxDate, -13 ) + 1, MaxDate )
VAR AvgHeadcount =
    AVERAGEX (
        DatesToUse,
        CALCULATE ( SUM ( Headcount[Headcount] ), Employees[Type] = "Permanent" )
    )
VAR TotalTerminations =
    CALCULATE (
        SUM ( Terminations[Terminations] ),
        DatesToUse,
        Employees[Type] = "Permanent"
    )
VAR Result =
    DIVIDE ( TotalTerminations, AvgHeadcount )
RETURN
    Result

Rolling 12 month termination voluntary % =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    DATESBETWEEN ( 'Date'[Year month], EOMONTH ( MaxDate, -13 ) + 1, MaxDate )
VAR AvgHeadcount =
    AVERAGEX (
        DatesToUse,
        CALCULATE ( SUM ( Headcount[Headcount] ), Employees[Type] = "Permanent" )
    )
VAR TotalTerminations =
    CALCULATE (
        SUM ( Terminations[Terminations] ),
        DatesToUse,
        Employees[Type] = "Permanent",
        Terminations[Type] = "Voluntary"
    )
VAR Result =
    DIVIDE ( TotalTerminations, AvgHeadcount )
RETURN
    Result

Rolling 12 month termination involuntary % =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesToUse =
    DATESBETWEEN ( 'Date'[Year month], EOMONTH ( MaxDate, -13 ) + 1, MaxDate )
VAR AvgHeadcount =
    AVERAGEX (
        DatesToUse,
        CALCULATE ( SUM ( Headcount[Headcount] ), Employees[Type] = "Permanent" )
    )
VAR TotalTerminations =
    CALCULATE (
        SUM ( Terminations[Terminations] ),
        DatesToUse,
        Employees[Type] = "Permanent",
        Terminations[Type] = "Involuntary"
    )
VAR Result =
    DIVIDE ( TotalTerminations, AvgHeadcount )
RETURN
    Result