cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AmandaMulryan
Frequent Visitor

HR Employee Headcount Matrix

I'm trying to create a matrix that allows me to display the start of month (SOM) and end of month (EOM) employee headcounts by month and by driver type or by terminal. The formulas I currently have are correct for the totals, but do not work in the matrix (i.e. cannot be filtered by the driver type or terminal). I've included a screenshot of the matrix and the DAX formulas for the current SOM and EOM headcounts. How do I write this so that they are filtereable in the matrices by terminal or driver type? Everything I've tried does not give correct counts. 

 

AmandaMulryan_1-1661180554741.png

 

 

Total Driver Count SOM =
VAR selectedDate = MIN('Date'[Date])

RETURN

SUMX(ALL(Drivers),
VAR employeeStartDate = [HiredOn]
VAR employeeEndDate = [TermedOn]
RETURN IF(employeeStartDate <= selectedDate && OR(employeeEndDate >= selectedDate, employeeEndDate = BLANK()),1,0))
 
 
Total Driver Count EOM =
VAR selectedDate = MAX('Date'[Date])

RETURN

SUMX(ALL(Drivers),
VAR employeeStartDate = [HiredOn]
VAR employeeEndDate = [TermedOn]
RETURN IF(employeeStartDate <= selectedDate && OR(employeeEndDate >= selectedDate, employeeEndDate = BLANK()),1,0))
2 REPLIES 2
amitchandak
Super User
Super User

@AmandaMulryan , Use Min and Max on date of date column, in formula used in blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Or refer file attached. Use Min/Max for start and end of month  on date of date table in measure

I have that already written out in the formulas I listed. The formulas I listed are correct with the totals. However, as seen in the screenshot, they do not calculate correctly BY driver type or BY terminal, as well as per month. I am looking to get the total SOM and EOM counts per month, PER Terminal or PER driver type. They will only give me the overall total.

Helpful resources

Announcements
Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.