Calculate Turnover - 12 months Rolling Average (wrong totals showing)

[Juma1231]

Hi all,

I'm facing an issue with calculating employees turnover. Not sure what is wrong here. Hope you guys can help.

So I'm attaching a sample data to show exactly the problem I'm facing.

Here is the Main Employees table: 

I have a date table : 

I'm trying to calculate turnover using the below :

– Number of employees who left
– Count of Employees at the end of the period (now)
– Count of Employees at the start of the period (12 months before)

For employees Count at the end of the period (now): I have used:

Employee Count = 

VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX('Employees',

VAR employeeStartDate = [Start Date]

VAR employeeEndDate = [End Date]

RETURN IF(employeeStartDate<=selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)

) 

For Count of Employees at the start of the period (12 months before): 

Employee Count 12 months before = 

VAR selectedDate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))

RETURN 
 SUMX('Employees',
 VAR employeeStartDate = [Start Date]
 VAR employeeEndDate = [End Date]
 RETURN IF(employeeStartDate<=selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)
 ) 

For Number of employees who left in the past 12 months:

Employees who left past 12 months = 

VAR selectedDate = LASTDATE('Date'[Date])
VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))

RETURN 
SUMX('Employees',
  IF([End Date]>=selectedDate12MonthsBefore && [End Date]<=selectedDate, 1, 0)
)

For Employee Turnover Rate :

Employee Turnover rate = 

VAR averageEmployeesRolling12months = ([Employee Count]+[Employee Count 12 months before])/2

RETURN
[Employees who left past 12 months]/ averageEmployeesRolling12months

Result I'm getting is as below:  The highlighted is what I couldn't explain why it's computed. Is it something wrong with these 2 measures?

Your help is highly appreciated. I have used Power BI file from Finance BI (link to the file here) 

It's exactly similar to what I'm facing right now with my data. 

Thanks in advance for your help.

[jdbuchanan71]

@Juma1231 

You already have the leavers in the rolling 12 months. [Employees who left past 12 months] is a dynamic rolling 12 month calc.  To get the rolling 12 month avg count would this.

12 mo avg count = 
CALCULATE(
    AVERAGEX(        
        VALUES('Date'[Year Month]),
        [Employee Count]),
        DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH)
)

[Juma1231]

Thank you very Much @jdbuchanan71 .

While I was building up the visuals using my data, I have noticed that if I use rolling avg. 12 months, it will not be the best to descride current year since we are only in Feb and it will overlap with last year's data. So, I thought of using YTD for the leavers. In order to do that I have created the below measure to show leavers by month. 

Employees who left current month = 

VAR _End = MAX ('Date'[Date] )

VAR _Start = EOMONTH ( _End, -1 ) +1

RETURN CALCULATE(

COUNTROWS( Employees ),

Employees[End Date] >= _Start && Employees[End Date] <= _End

)

But for YTD, the below measure won't work. 

Employees who left YTD = 

TOTALYTD([Employees who left current month], 'Date'[Date])

Any idea what's going wrong?

Thanks again!

[Juma1231]

I was able to figure it out. Thank you 🙂 

[Juma1231]

Hi there @jdbuchanan71 

What if I would like to calculate the average count ( 12 months rolling average) from the measure you created.Also, I would like to have the sum of leavers during the period (rolling 12 months )

So, at the end I will calculate turnover by dividing Sum of leavers during the period (rolling 12 months)/average count during the period (12 months)

Thank you again for any help you can give.

[jdbuchanan71 · Accepted solution]

@Juma1231 

In the sample file I noticed that the date table was not marked as a date table which can mess with time intelligence functions.  I also disabled auto datetime on the file since we don't need it with a date table.

Also the original employee count was only looking at the last day of the month so employees that left in a month were not counted in that month which I think is incorrect. 

I have adjusted the measures like so.

Employee Count = 
VAR _Start = MIN ( 'Date'[Date] )
VAR _End = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Employees ),
        Employees[Start Date] <= _End
        && ( Employees[End Date] >= _Start || ISBLANK ( Employees[End Date] ) )
    )

Employee Count 12 months before = 
CALCULATE(
    [Employee Count],
    SAMEPERIODLASTYEAR( 'Date'[Date] )
)

Employees who left past 12 months = 
VAR _End = MAX ('Date'[Date] )
VAR _Start = EOMONTH ( _End, -12 ) +1
RETURN CALCULATE(
    COUNTROWS( Employees ),
    Employees[End Date] >= _Start && Employees[End Date] <= _End
)

Employee Turnover rate = 
VAR averageEmployeesRolling12months = ( [Employee Count] + [Employee Count 12 months before] ) / 2
RETURN
    [Employees who left past 12 months] / averageEmployeesRolling12months + 0

I have also attached my updated sample file for you to look at.

[Juma1231]

Thank You @jdbuchanan71 for the great help. This is more than what I've expected.