topic Re: Rolling 12-Month Employee Turnover in DAX Commands and Tips

Rolling 12-Month Employee Turnover

Andonip — Thu, 12 Jun 2025 09:44:36 GMT

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:

Heacount - which has each period Jan'25, Feb'25 etc
Terminations - File with our leavers, termination dates and then categorised by voluntary and involuntary
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,

Re: Rolling 12-Month Employee Turnover

johnt75 — Thu, 12 Jun 2025 10:34:50 GMT

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

Re: Rolling 12-Month Employee Turnover

v-kpoloju-msft — Thu, 12 Jun 2025 12:00:01 GMT

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.

Re: Rolling 12-Month Employee Turnover

Andonip — Fri, 13 Jun 2025 12:54:42 GMT

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

Re: Rolling 12-Month Employee Turnover

johnt75 — Fri, 13 Jun 2025 13:35:37 GMT

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

Re: Rolling 12-Month Employee Turnover

Andonip — Fri, 13 Jun 2025 14:42:06 GMT

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?

Re: Rolling 12-Month Employee Turnover

johnt75 — Fri, 13 Jun 2025 14:47:37 GMT

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.