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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
mdalton
Helper I
Helper I

CountIF Active Employee Based on Hire/Term Date

I am currently updating AWS SQL data in PowerBI to get the most updated hire/term dates for employees.

I then copy a table with employee ID, hire date, and term date into excel. In a new sheet I have columns:

WeekOf, WeekEnding, and Active Employees

For Active Employees, I use formula:
=IF(WeekOfValue1>TODAY(),0,COUNTIFS(HireDateColumnFromOtherSheet,"<="&WeekOfValue1,TermDateColumnFromOtherSheet,">="&WeekEndingValue1))

 

My company hires a lot of employees daily so I must reselect the hire and term column every day.

I would much rather move this whole process into PowerBI for ease/automation. Let me know if this is possible.

1 ACCEPTED SOLUTION
mdalton
Helper I
Helper I

I am going to answer my own question:

 

Import excel sheet of WeekOf and WeekEnding named Week

 

New Column:

ActiveEmployees = if(Week[WeekOf]>TODAY(),0,CALCULATE(COUNTA(Hires_Terms_Summary[Employee ID]),FILTER(all(Hires_Terms_Summary),and(Hires_Terms_Summary[Hire Date]<=Week_Branch[WeekEnding]),Hires_Terms_Summary[TermDate]>Week_Branch[WeekEnding])))

Worked for me and verified it against my excel sheet.

View solution in original post

1 REPLY 1
mdalton
Helper I
Helper I

I am going to answer my own question:

 

Import excel sheet of WeekOf and WeekEnding named Week

 

New Column:

ActiveEmployees = if(Week[WeekOf]>TODAY(),0,CALCULATE(COUNTA(Hires_Terms_Summary[Employee ID]),FILTER(all(Hires_Terms_Summary),and(Hires_Terms_Summary[Hire Date]<=Week_Branch[WeekEnding]),Hires_Terms_Summary[TermDate]>Week_Branch[WeekEnding])))

Worked for me and verified it against my excel sheet.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.