Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 ID | Emp Name | Department | Level | Joining Date | Exit Date | Status |
YYYY0001 | aaa | xyz | Level2 | 15-May-21 | 14-Nov-21 | Exited |
YYYY0002 | bbb | xya | Level5 | 14-Feb-21 | 31-Mar-99 | Active |
YYYY0003 | ccc | xyb | Level3 | 9-Jul-21 | 31-Mar-99 | Active |
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:
Month | Closing Headcount | Effective Headcount |
Feb'21 | 1 | 0.50 |
Mar'21 | 1 | 1.00 |
Apr'21 | 1 | 1.00 |
May'21 | 2 | 1.52 |
Jun'21 | 2 | 2.00 |
Jul'21 | 3 | 2.71 |
Aug'21 | 3 | 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
Solved! Go to Solution.
@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.
@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.
Awesome Greg, this is exactly what I was looking for 🙂
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!