12 month rolling turnover

[Nabil20_24]

I want to create a 12-month rolling turnover rate measure in Power BI and use this measure in a bar chart to display the values for each month from January to December for each specified year. To do this, I created a DateTable in Power BI and connected it to my HR data using a one-to-many relationship. I then used the Year from the DateTable in a slicer. The Year is selected from the DateTable.

I created the following measures: EmployeeAtEndOfMonth, EmployeeAtStartOfMonth, and AverageEmployeeRolling12Month. While EmployeeAtEndOfMonth and EmployeeAtStartOfMonth are working fine, the AverageEmployeeRolling12Month measure is returning incorrect values, which is impacting the final results.

The idea is to use the 12-month rolling turnover in a line chart, where the X-axis represents the months and the Y-axis represents the values of the 12-month rolling turnover for each month. For example, if I select the year 2024 from the slicer, the calculation for January should be as follows:

1. Calculate the average number of employees by adding the cumulative employees in January 2024 and the cumulative employees in January 2023, then dividing by 2.
2. Calculate the number of employees who left between January 2023 and January 2024.
3. Finally, calculate the 12-month rolling turnover by dividing the number of employees who left between January 2023 and January 2024 by the average number of employees between 2023 and 2024
   
   

DateTable = ADDCOLUMNS (
     CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date])
)​

CurrentEmployees = 
 CALCULATE(
    COUNTROWS(HRData), 
    HRData[EmploymentStatus] = "Still", 
    HRData[StartDate] <= MAX('DateTable'[Date]),
    NOT(HRData[EmploymentType] IN {"Contractors"}),
    FILTER(
    ALL('DateTable'), 
    'DateTable'[Date] <= MAX('DateTable'[Date])  
    )

 )​

NumberofEmployee12monthsbefore = 
CALCULATE(
    [CurrentEmployees],
    SAMEPERIODLASTYEAR( DateTable[Date]),
        FILTER(
        ALL('DateTable'), 
        'DateTable'[Date] <= MAX('DateTable'[Date])  
    )
)

AverageEmployeesRolling12Months  = [currentEmplyees] + [NumberofEmplyee12monthsbefore] / 2 

Leavers = 
VAR SelectedMonth = max(DateTable[Date])
VAR StartDate = EDATE(SelectedMonth, -12) 
VAR EndDate = SelectedMonth
Return 
 CALCULATE(
    COUNTROWS(HRData), 
    HRData[Employemntstatus] = "No longer in the business", 
    HRData[End_Date] >= EDATE(SelectedMonth, -12) && HRData[End_Date] <= EndDate,
     ALL('DateTable')
 )

[v-hashadapu · Accepted solution]

Hi @Nabil20_24 , Hope the solution worked for you. can you please confirm this by marking it 'Accept as Solution', so others with similar queries may find it easily. If it didn't work, please share the details and we can work together to solve it.

[v-hashadapu]

Hi @Nabil20_24 , Just wanted to confirm if your issue is solved.

If it is, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Nabil20_24 , Can you please let me know if your issue is resolved. 

If not, please share the details. if it is solved, then please consider marking it 'Accept as Solution', so others with similar queries may find it more easily. 

[Nabil20_24]

no unfortuanatly not, still seeing the value for December's AverageEmployeesRolling12Months across all months from January to December

[v-hashadapu]

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.

[Nabil20_24]

Thanks a lot for your helop,

Everything works fine except for the AverageEmployeesRolling12Months, which appears to be unaffected by the slicer. It consistently returns the value for December's AverageEmployeesRolling12Months across all months from January to December, regardless of the year selected in the slicer or the month displayed on the x-axis

[v-hashadapu]

Hi @Nabil20_24 , Sorry to know it didn't work for you.

Can you please try these modified DAX formula:
AverageEmployeesRolling12Months =
VAR CurrentMonthEmployees = [CurrentEmployees] -- This gives the number of employees for the current month
VAR LastYearEmployees =
CALCULATE(
[CurrentEmployees],
SAMEPERIODLASTYEAR(DateTable[Date]) -- Getting the number of employees in the same month last year
)
RETURN
(CurrentMonthEmployees + LastYearEmployees) / 2

If this helps, please mark it 'Accept as Solution', if not please share the details, for us to better help you.

[Nabil20_24]

still same problem 

[v-hashadapu]

Hi @Nabil20_24 , 

Can you please go over the attached Pbix file and see if it helps solve the issue.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...