cancel
Showing results for
Did you mean:
Helper I

## Calculations with DAX Filters - Voluntary/Involuntary Staff Turnover

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])
))

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?

1 ACCEPTED SOLUTION
Community Support

Hi, @leont3

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.

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.

4 REPLIES 4
Community Support

Hi, @leont3

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.

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.

Super User IV

@leont3 , refer to my blog this

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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))
)

Proud to be a Super User!

Helper I

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.

Super User IV

@leont3 , 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))

Proud to be a Super User!

Announcements