cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leont3
Helper I
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 IDEmployee NameHire DateLocationTermination DateTerm CodeVoluntary/Involuntary
1Joe Bloggs20 March 2020NY30 April 2020VVoluntary
2Kevin Smith30 April 2018SF20 December 2021VVoluntary
3Charlotte Green30 March 2019SF10 July 2020TInvoluntary
4Penelope Brown10 July 2018NY   

 

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? 
 
1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @leont3 

 

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

4.png

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.

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

Hi, @leont3 

 

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

4.png

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.

View solution in original post

amitchandak
Super User IV
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))
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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. 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors