Power BI: HR Analytics - Employees as on Date - userelationship , crossfilter, Hired/Terminated

[@lyzaevaristo]

Thank you Amit! I have been looking for a solution to the monthly backlog report I am creating and this answers my problem! Great work with the detailed explanations on each measures!

[@AmitChandak]

Thanks, Glad it helped. Hope you will like new version too Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc

[@obazeeghosa]

Thanks for this very excellent video and the accompanying resources. Greatly appreciated.

[@AmitChandak]

Glad it was helpful!

[@mohanjanakiram8235]

Thank you Amit..!! Quick and precise..!

[@AmitChandak]

Thanks

[@hopefarlow4978]

Thank you Amit! How would you show the current headcount of employees for each month if the employees were in different departments, such as Engineering, HR, Logistics, Supply Chain using a stacked Bar chart?

[@AmitChandak]

I think the same measures should work. Unless one employee has more than one department. Please check the file, I added the department www.dropbox.com/scl/fi/y7q6ht9np1yb4cfbzzyj5/HR-With-Dept.pbix?rlkey=2en7z9zs2lxu7wqqsy3rn2q3k&dl=0

[@fmsmz]

I worked hard to get this video. For two weeks I have been trying to implement the same idea and failed. I will try it and give you the results.

[@fmsmz]

Its working finally, you are the best ever

[@AmitChandak]

Hope you will like the newer version of this Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc Hope you will likes these series too Mastering Power BI: kzbin.info/www/bejne/rafWcq2Kprh3jMk Expertise Power BI: kzbin.info/www/bejne/a2qzhnmrqqilmLs Mastering Microsoft Fabric: kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@fmsmz]

​@@AmitChandakThank you sir, I will continue to increase my experience through your channel

[@hendrag593]

Thank you very much for this great video. GBU.

[@AmitChandak]

Thanks 🙏. Hope you will like the new version too. kzbin.info/www/bejne/nKfKlKtuhrGkkLc

[@lijunchen]

Bravo! Amit. This video is very helpful. This works well as point-in-time counts of active cases across time in my human service work. I have a question for you. Besides the active employee counts, can you create a measure to show the average and median tenure (from hire date until current period start or end, in days) of the active employees in any period. Also can you also create the average age of employees if we have their birthdates? And a measure like average salarys?

[@AmitChandak]

Glad it helped. Please check the another video on channel Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc Also check out Complete Power Bi Video with 100+ Topics: kzbin.info/www/bejne/mX-bcoJplNugors Mastering Power BI Series: kzbin.info/www/bejne/rafWcq2Kprh3jMk Expertise Power BI Series: kzbin.info/www/bejne/a2qzhnmrqqilmLs

[@lijunchen]

@@AmitChandak Very helpful. Thanks a lot.

[@markdixon333]

Really helpful Amit - straightforward explanations and easy to replicate, thank you for your help!

[@AmitChandak]

You are most welcome. Related blog - community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970 File link www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0 Without using active join - www.dropbox.com/s/ixftnm99hg3wmu8/Current_employee_without%20Join.pbix?dl=0

[@mudrasasidhar]

Fantastic !!!! Thank You!!!

[@AmitChandak]

Glad you liked it! Thanks 🙏 Hope you will also like Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc

[@SSeerrooyy]

excellent video! was looking for something like this for ages. trying now to implement it. I'm using the same method to calculate how many projects were active on each day. each project has start date and end date. several questions: do the relations from both end date and start date in the project table to the date field in the calendar should be inactive? if yes, this is impacting other visuals in my report. any workaround? second question - seems I can't use any other slicers on the report page (i have slicers for project region for example). I was hoping i could use this to show a number of concurrent projects for each region.

[@AmitChandak]

Thanks. If you want to keep both dates inactive, please find the file link. Let me know if you need more information. You should be able to use other slicer joined with this table www.dropbox.com/s/ixftnm99hg3wmu8/Current_employee_without%20Join.pbix?dl=0

[@nhbkj166]

Is it possible to swap the rows and columns of this table in Power BI? I wanted to arrange the measures horizontally and the months vertically because there are many measures, but due to the specifications of Power BI, I couldn't place the measures in the rows. Does my approach go against some fundamental concept in Power BI?

[@AmitChandak]

You can so that in matrix visual, Switch values to rows previously know as show values on rows

[@Datanox]

Hi Amit, Thanks for sharing the knowledge. However, I have a question as to how to calculate hire & terminations for any period based on all position history of staff. The last start date & end date acting as his recent position. Also, if he's continuing employee, the end date would be blank.

[@AmitChandak]

Do you have more than one entry for an employee. Can share sample for source and output

[@Datanox]

@@AmitChandak Yes. More than one entry for employee representing historical agreement dates. The input table represents like this community.powerbi.com/t5/Desktop/Hiring-termination-Active-Headcount-based-on-historical-data/m-p/1889239#M725262

[@jacques8290]

@@Datanox did you work this out? I am having the same issue

[@sridrigger]

This helped me a lot.. Thanks :) Any idea how to get cumulative exits for the same as above with exit and join dates

[@AmitChandak]

You already have Hired and Terminated employee. refer if needed community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970 Then you can have measure like Cumm Hired = CALCULATE([Hired Employee],filter(all('Date'),'Date'[date]

[@AmitChandak]

@@sridrigger Opening Headcount= CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]< Min(d_DateTable[Date]) && (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>=Min('d_DateTable'[Date]))), (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None)) Closing Headcount= CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]max('d_DateTable'[Date]))), (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

[@sridrigger]

@@AmitChandak Sincere thanks Worked like a charm :) Was hung up with some miscalculations by 3 days

[@sridrigger]

Sorry For the trouble Amith one Last help I have calculated the Annual Attrition by divide( exitcount, avg headcount) but i need the same as below Attrition * (12which is year count/Month number) which if its april 1, may 2 and so on based on fiscal calendar how can this be achieved Annualized Attrition

[@sridrigger]

@@AmitChandak Any Help on Annual Attrition Calculation?

[@aarondnice9018]

Thanks for the great and easily explained video Amit. Just had one question, When I am replicating "Last Period Employee" measure, it is excluding count of employees who joined on that particular month. Where should I alter the dax to fix this? Thanks again!

[@AmitChandak]

Have you used formula in my file ? Last Period Employee = var _min_date = minx(all('Date'),'Date'[Date]) var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR))) Return CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None)) To me the number seem fine. If not please share a sample file My file link: www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

[@hiteshdangodra6440]

Dear Amit, Thank you so much for this vedio. One help required desperately how to find current active employees growth from last date of previous year , your help make lots of love ❤

[@AmitChandak]

Please try this measure Last Year Employee = var _min_date = minx(all('Date'),'Date'[Date]) var _Expression=maxx('Date',DATEADD('Date'[Date],-1,YEAR)) Return CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None)) You can also refer Last Period Employee in the file www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

[@christianmancini2017]

Hi Amit, Thanks for the help, this was simple a really helpfull. It works excellent! I have a question, when I click on the table and in "Terminations", it shows me the hiring names, not the terminated ones, is there a way to solve it? Another question, is there a way to calculate the attrition rate or turnover using this numbers? Thanks a lot again!

[@AmitChandak]

Are using drill through? the Terminations measure is possible because userelationship and not because of the join, we need make sure the table where we are looking for Termination's name is having Terminations as measure too

[@yousifteedonmoye619]

Great video, please how do I add the “||“ or operator in power bi? What do I need to press on the keyboard to make it appear Coz tried everything it's not working at my computer

[@AmitChandak]

Press pipe sign twice on keyboard. You have or function too

[@GanessenSaayman]

Hi, how do i include a measure that looks at All Employees per month. i.e Terms + Hires + Current employees

[@AmitChandak]

Current employee should consider all active including hire and terminations. Can you please elaborate. We can discuss more on LinkedIn - www.linkedin.com/in/amitchandak78/ Refer if this can help Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc

[@JimBuckleyBarrett]

Thank you! That worked!

[@AmitChandak]

Glad it helped! Thanks. Hope you will like the next one too: Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: kzbin.info/www/bejne/nKfKlKtuhrGkkLc

[@nikhilnikk5569]

Easy to understand Thanks sir

[@AmitChandak]

Thanks and welcome