Calculating Staff Turnover In Power BI Using DAX - HR Insights

Find out how to do staff turnover calculation and gain insights on how many staff you have or how many staff have left over any period of time.
**** Video Details ****
0:25 - introducing staff turnover
1:06 - data model consideration/inactive relationships
2:10 - DAX formula for current staff at any time
2:45 - staff turnover calculation using CALCULATE function
4:02 - staff turnover formula results
5:10 - visualization review for staff turnover numbers
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ulti...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ulti...
FREE - Power BI Resources - enterprisedna.co/power-bi-reso...
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-...
Learn more about Enterprise DNA - www.enterprisedna.co/
Enterprise DNA Membership - enterprisedna.co/membership
**** Related Links****
Total Number Of Staff Over Time - • Total Number Of Staff ...
How To Manage Multiple Date Calculations In Your Fact Tables - Advanced Power BI - • How To Manage Multiple...
How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/2019/02...
**** Related Courses****
Solving Analytical Scenarios w/Power & DAX - portal.enterprisedna.co/p/solv...
Business Analytics Series - portal.enterprisedna.co/p/busi...
Unique Analytical Scenarios - portal.enterprisedna.co/p/uniq...
**** Related Support Forum Posts****
Staff Population - forum.enterprisedna.co/t/staf...
How to calculate Headcount over time - forum.enterprisedna.co/t/how-...
Salary Forecast - forum.enterprisedna.co/t/sala...
People analytics: headcount - forum.enterprisedna.co/t/peop...
Employee count by date - forum.enterprisedna.co/t/empl...
For more related posts see here - forum.enterprisedna.co/search...

[@EnterpriseDNA]

***** Related Links***** Total Number Of Staff Over Time - kzbin.info/www/bejne/q4jTqI2Dm7VopNU How To Manage Multiple Date Calculations In Your Fact Tables - Advanced Power BI - kzbin.info/www/bejne/qGnIkJWIf5VqhJI How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/2019/02/04/working-with-multiple-dates-in-power-bi/ ***** Related Support Forum Posts***** Staff Population - forum.enterprisedna.co/t/staff-population/2673 How to calculate Headcount over time - forum.enterprisedna.co/t/how-to-calculate-headcount-over-time/2908 Salary Forecast - forum.enterprisedna.co/t/salary-forecast/2377 People analytics: headcount - forum.enterprisedna.co/t/people-analytics-headcount/2318 Employee count by date - forum.enterprisedna.co/t/employee-count-by-date/67 For more related posts see here - forum.enterprisedna.co/search?q=staff%20count ***** Related Courses***** Solving Analytical Scenarios w/Power & DAX - portal.enterprisedna.co/p/solving-business-scenarios-with-power-bi-and-dax Business Analytics Series - portal.enterprisedna.co/p/business-analytics-series Unique Analytical Scenarios - portal.enterprisedna.co/p/unique-analytical-scenarios

[@gobeyond5429]

Hi. I am new to Power BI am using the "Calculating Staff Turnover In Power BI Using DAX - HR Insights" video and when I try to duplicate the results in video, the "Current Staff" measure returns the exact number of the terminated employees (2,316) instead of the total current employees (11,080) and the "Turnover Staff" measure returns only 2 when it should return the 2,316. Is there anyone who can help me understand what is happening? I don't have a premium LinkedIn to reach out to Sam McKay, CFA.

[@SolutionsAbroad]

I wish I found this before I had to build it out myself.. thanks for sharing!

[@EnterpriseDNA]

Hi Power BI Tutorials from Solutions Abroad, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@brunobentancor7172]

Excellent

[@rdg515]

After thinking about this for a little while I think I answered my own question... Firstly, Filter is not iterating the dates, it's iterating the records in the fact / history table. Nevertheless, at a row level, Min and Max 'Date'[Date] evaluate to the same date; there's no difference in using either . However for the Total, Min('Date'[Date]) is the min date in the table.

[@rdg515]

Sam, if Filter is an iterator that evaluates every date why does it matter whether you use min or max? Shouldn't min or max only see one date value per iteration and therefore be interchangeable?

[@benjaminkhammar7395]

Hi Sam - thank you for doing this, it was extremely helpful! I do have one question - I have a "Labor Category" column in a "Categories" table that has an active relationship (1 to many) to a "Labor Category" column in my equivalent to your "Staff Population" table. When I add a slicer to the turover line chart (or a filter applied to the page), there is no change. How do I get a slicer or page filter to apply? Essentially, i just need for my "Staff Count" and "Staff Turnover" calculations to exclude any employees that are not the selected labor categories.

[@matiasserrenho7067]

Hi! Thanks a lot for the video, it was very useful to me. I'm though still stuck with something. I have rehires. So I have termination dates already in for active employees. so the filter does not work 100% right as it is not picking up those re hires. Can you think of anything to solve this?

[@EnterpriseDNA]

Hi Matias, you can browse this topic in the Support Forum to see if the same scenario has been asked. forum.enterprisedna.co/

[@josbar80]

I had a similar problem, I replaced the null value in the data with a date later than today() and changed the Current Staff Dax to >= Today() instead of = Blank() and

[@hiteshdangodra6440]

Super, excellent, just one question how to find out active head count for any date. If we do filter then it will show filter active head count, so please help

[@EnterpriseDNA]

Hi Hitesh, For getting Head Count in case of any Filter, please make use of ALL, ALLSELECTED functions. At Enterprise DNA forum forum.enterprisedna.co/ there are numerous examples that will be helpful to you. Also, you can refer Enterprise DNA DAX guide info.enterprisedna.co/dax-guide/dax-function-guide/ to get more information on these functions.

[@LuisRomaUSA]

I think the MIN should be a MAX, such as: current Staff = CALCULATE([Employee no], FILTER(VALUES(Main[DateOfEmployment] ), 'Main'[DateOfEmployment] = MAX('Date'[Date] ), ISBLANK(Main[DateOutOfEmployment] ))))...otherwise you only get all employees that have ever worked for you, without discounting for the leavers

[@daniellucas5708]

Thank you!

[@KMF7955]

Hi Sam, given that there are Inactive relationships can you arrive at the same result by using the USERELATIONSHIP function to join the relevant date from the ‘Staff Population’ table to the ‘Dates’ table?

[@EnterpriseDNA]

Hello Karen, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. In this case, "USERELATIONSHIP()" function cannot be used because start and end dates are compared with the dates tables in order to check whether they fall within the selected date range or not whereas "USERELATIONSHIP()" function is used to activate one relationship at a time and then calculate the results as per that set of dates. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful! You can subscribe to our KZbin channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@sykatrys5127]

i created a table date with calendar() but it didn't work i have the full staf members it dosen't filter by date , i did everything right

[@danielwelsh1578]

This video is great in showing current staff over time, but most companies aren't calculating turnover as a raw number like that. They're taking the count of people that quit divided by the avg. count of active employees throughout the month to get a % number. Would you be willing to show the DAX on how you would calculate that?

[@EnterpriseDNA]

Hi Daniel, Thanks for the Inputs. This shall be possible using DAX. We can add to planned future videos. For additional info, you may explore the Enterprise DNA Forum for discussions on this topic forum.enterprisedna.co/ Thanks

[@martinnoah9716]

@@EnterpriseDNA This would be great, thank you. I cannot find anything worthwhile for monthly turnover % as Daniel Welsh described.

[@radekou]

Hi Sam - thanks for the video - neat solution. One thing though - I believe the inactive relationships are not relevant for this scenario - it works the same way whether they are there or not (tested). "Inactive" means they're not really doing anything by default (but can be turned on a measure level). Thanks

[@lindabond9307]

I have the same issue as some other commentors - the current emp over time calc worked fine, while the departed employee calc didn't work at all. Can't see any error in the DAX. The Turnover Calc gives me EVERY departed employee from beginning of records up to the period I nominated and did not distinguish for the period / day.

[@EnterpriseDNA]

Hello Linda, Thank you for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model, measures and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

[@lindabond9307]

@@EnterpriseDNA Thanks - will do

[@hiteshdangodra6440]

Sir please help to find moving average of current staff. For eg. Jan has 10, Feb has 20, Mar 30 So moving avg for Jan is 10 Feb is 15 and mar is 20, please help 🙏

[@EnterpriseDNA]

Thank You for posting your query onto our channel. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA KZbin channel pertaining to topic - "Rolling or Moving Average". For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/ blog.enterprisedna.co/showcasing-trends-using-moving-average-techniques-in-power-bi/ blog.enterprisedna.co/calculating-a-rolling-average-in-power-bi-using-dax/ kzbin.info/www/bejne/g3mugZmlg6qserc kzbin.info/www/bejne/g32kkGyvhrd9nq8 kzbin.info/www/bejne/o6OqmXt4pauLf7M

[@AbhishekRajput-vu9hu]

could you share the exercise files for the practice.

[@EnterpriseDNA]

Hi, resource file is available in the Enterprise DNA Online which is accessible via memebrship portal.enterprisedna.co/ enterprisedna.co/membership

[@ianboothman540]

Good Afternoon, i have just mirrored this video and my staff turn over is not working, It seems to be claculatinf total leaves all time I have used the code: Staff Turnover = CALCULATE(COUNTROWS('Staff Population'), FILTER(VALUES('Staff Population'[End Date]), 'Staff Population'[End Date]

[@EnterpriseDNA]

Hello Ian, Thank you for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference since your data model will be different than ours. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@benjaminkhammar7395]

I had a similar issue and resolved it by changing the second line. Note that the MIN() formula is gone and the ".[Date]" property was added. However, I didn't add the employee/turnover count columns to the Date table. I added a "StaffCount" table with an "AsOfDate" column. FILTER(VALUES('Staff Population'[End Date]), 'Staff Population'[End Date]

[@LadyMuck88]

Can hear this guys siliva and mouth slapping around, maybe mic needs adjusting. Just cannot listen because of this.