Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
amitksingh2003
Regular Visitor

Calculating Effective headcount

Hello Experts,

 

I am trying to create a Headount/FTE dashboard. I have employee records like below table. I have also added a calendar table for time slicer. Everything works great. Thanks to this wonderful forum. I came here whenever stuck and plenty of previous similar problems/solutions to refer to.

 

Emp IDEmp NameDepartmentLevelJoining DateExit DateStatus
YYYY0001aaaxyzLevel215-May-2114-Nov-21Exited
YYYY0002bbbxyaLevel514-Feb-2131-Mar-99Active
YYYY0003cccxybLevel39-Jul-2131-Mar-99Active

 

I wanted to further extend this dashboard to be used as a tool for heacount/FTE forecast. I am trying to calculate effective headcount for this purpose. I mean when I am calculating effective headoucnt for the month of say Feb'21, employee ID YYYY0002 should be counted as 0.5 instead of 1 as the employee has joined in the middle of the month. 

 

The Summary HC table should look like below:

MonthClosing HeadcountEffective Headcount
Feb'211                         0.50
Mar'211                         1.00
Apr'211                         1.00
May'212                         1.52
Jun'212                         2.00
Jul'213                         2.71
Aug'213                         3.00

 

I am using below measure to get headcount numbers successfully. However, stuck at effective headcount calculation. Should I use Measures or add custom columns!

 

 

 

CALCULATE(DISTINCTCOUNT('Employee_Table'[Emp ID]),FILTER(ALL('Calendar'[Date]),[Min Date]=[Min Date]),'Employee_Table'[Joining Date]<MIN('Calendar'[Date]))

 

 

 

 

I tried below measure to get employee level effective headcount number. This seems wrong because of the way I am trying to get employee level Exit/Joining date. It's actually giving Min/Max date from column, can be for any employee. How do I get employee level Exit/Joining Date!

 

 

 

(MIN(MIN('Employee_Table'[Exit Date]),MAX('Calendar'[Date]))-MAX(MIN('Employee_Table'[Joining Date]),MIN('Calendar'[Date])))/(MAX('Calendar'[Date])-MIN('Calendar'[Date])+1)

 

 

 

 

Thanks

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@amitksingh2003 I have an FTE calculation in DAX Cookbook, Recipe 5 in Chapter 7: PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com) That's a link to the GitHub repository for the PBIX files used.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@amitksingh2003 I have an FTE calculation in DAX Cookbook, Recipe 5 in Chapter 7: PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com) That's a link to the GitHub repository for the PBIX files used.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Awesome Greg, this is exactly what I was looking for 🙂 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.