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

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors