Calculations with DAX Filters - Voluntary/Involuntary Staff Turnover

[Anonymous]

Hi all,

I am stuck into calculating Voluntary/Involuntary Staff Turnover for my organisation. I have managed to calculate overall turnover, however, am having trouble when I need to distinguish between the two.

This is what the sample data looks like (Title, Emp Master File): 

Employee ID	Employee Name	Hire Date	Location	Termination Date	Term Code	Voluntary/Involuntary
1	Joe Bloggs	20 March 2020	NY	30 April 2020	V	Voluntary
2	Kevin Smith	30 April 2018	SF	20 December 2021	V	Voluntary
3	Charlotte Green	30 March 2019	SF	10 July 2020	T	Involuntary
4	Penelope Brown	10 July 2018	NY

It is also connected with a date table.

As you may be aware, annualised turnover is calculated through: 

Past 12 Month Leavers / (Headcount at beginning of period + headcount at end of period)/2 

In order to achieve this, I have the following calculations: 

Past 12 Month Leavers =

VAR selectedDate = LASTDATE('Date Table'[Date])

VAR selectedDate12MonthsBefore =

(SAMEPERIODLASTYEAR(selectedDate))

RETURN

CALCULATE(sumx('Emp Master File',

IF([Termination Date]>=selectedDate12MonthsBefore && [Termination Date] <= selectedDate,1,0)),

NOT(ISBLANK('Emp Master File'[Termination Date])

))

Headcount (12 Month Prior) =

VAR selectedDate =

NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date Table'[Date])))

RETURN

SUMX('Emp Master File',

VAR employeeStartDate = [Hire Date]

VAR employeeEndDate = [Termination Date]

RETURN IF(employeeStartDate <= selectedDate &&

OR (employeeEndDate >= selectedDate, employeeEndDate=BLANK()),1,0))

Active Employees =

CALCULATE

(COUNTX(FILTER('Emp Master File',

'Emp Master File'[Hire Date] <=MAX('Date Table'[Date])

&&

(ISBLANK('Emp Master File'[Termination Date])

||

'Emp Master File'[Termination Date] > MAX('Date Table'[Date]))), 'Emp Master File'[Employee ID]),

CROSSFILTER('Date Table'[Date], 'Emp Master File'[Hire Date], NONE))

I have then used all of these to create an employee annualised turnover being:

Annual Turnover Rate =

[Past 12 Month Leavers]/[12 Month Rolling Average]  

Note: 12 Month Rolling Average is just the average of the active employees and the 12month prior headcount

This provides me with the annual turnover rate. However, now I would like to use the 'Voluntary/Involuntary' information in my table, and only calculate those that have left 'voluntary'. This means that the number of leavers calculation needs to only count those that have a 'V' in the Voluntary/Involuntary table. 

I was thinking of using the CALCULATE/FILTER function to achieve this in my 'Past 12 Month Leavers calculation' but it didn't seem to work: 

Past 12 Month Voluntary Leavers =

VAR selectedDate = LASTDATE('Date Table'[Date])

VAR selectedDate12MonthsBefore =

(SAMEPERIODLASTYEAR(selectedDate))

RETURN

CALCULATE(sumx('Emp Master File',

IF([Termination Date]>=selectedDate12MonthsBefore && [Termination Date] <= selectedDate,1,0)),

'Emp Master File'[Voluntary/Involuntary] = "V")

However, when I have done this, I end up with a blank. How would you recommend I use the same calculation as above but only consider those with a V or those with an I? 

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think the problem is that the column name and value don't match.

You need to modify the code: 'Emp Master File'[Voluntary/Involuntary] = "Involuntary") or 'Emp Master File'[Term Code] = "V")

It is recommended that you use countx() function to calculate, the usage of calculate (sumx(if)) is not common.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer to my blog this

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Terminated in last 12

 = CALCULATE(CALCULATE(COUNT(Employee[Employee Id ]),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

last 12 month avg

AverageX(values(Date[Month Year]),CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
)

[Anonymous]

Hi Amit,

Thanks for your response, but that was not what I was after unfortunately. 

I wanted to calculate only those employees who have been voluntary terminated using my formulas above. I hope that makes sense. 

[amitchandak]

@Anonymous , modification on this formula should help

Terminated in last 12

= CALCULATE(CALCULATE(COUNT('Emp Master File'[Employee Id ]),USERELATIONSHIP(Employee[Termination Date],'Date'[Date]),not(ISBLANK(Employee[Termination Date])), 'Emp Master File'[Voluntary/Involuntary] = "V"),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))