12 month rolling turnover

Hi Nabil20_24 ,Thank you for reaching out to Microsoft Fabric Community Forum.

I think the issue in your AverageEmployeesRolling12Months formula is that the addition and division are not properly given, leading to incorrect results. It needs to be modified to make sure the sum is calculated first and then divided by 2. So it should be:

AverageEmployeesRolling12Months =

    ([CurrentEmployees] + [NumberofEmployee12monthsbefore]) / 2

Your CurrentEmployees measure looks correct, just make sure that it accounts for all necessary filters and properly evaluates the employees who are still active at the current point in time.

your NumberofEmployee12monthsBefore measure seems to be using the SAMEPERIODLASTYEAR() function correctly, but confirm it works as expected.

Your Leavers measure is where we need to pay extra attention. You're using the EDATE function to look back 12 months, which is correct, but the key thing is ensuring that you're counting the leavers accurately during the previous 12-month period. Also, you may want to explicitly check that the leavers' End_Date falls between the start and end dates.

So, let’s modify it a bit:

Leavers =

VAR SelectedMonth = MAX('DateTable'[Date])

VAR StartDate = EDATE(SelectedMonth, -12)

VAR EndDate = SelectedMonth

RETURN

    CALCULATE(

        COUNTROWS(HRData),

        HRData[EmploymentStatus] = "No longer in the business",

        HRData[End_Date] >= StartDate && HRData[End_Date] <= EndDate,

        ALL('DateTable')

    )

The final step is to calculate the 12-month rolling turnover rate.

RollingTurnoverRate =

VAR AvgEmployees = [AverageEmployeesRolling12Months]

VAR LeaversCount = [Leavers]

RETURN

    IF(

        AvgEmployees <> 0,

        DIVIDE(LeaversCount, AvgEmployees) * 100,

        BLANK()

    )

Note: Once you've added these measures, you can visualize the 12-month rolling turnover rate in a bar chart. Once you've added these measures, you can visualize the 12-month rolling turnover rate in a bar chart. Drag your RollingTurnoverRate measure onto the Y-axis. Use the Year slicer to filter the data to a specific year. Ensure that your DateTable is marked as a Date table in Power BI (using the "Mark as Date Table" option), and that the relationship between the DateTable and HRData is set correctly (one-to-many).

If you think this post helps, please mark it as Accept as Solution, so others with similar queries may find it more easily.