CountIF Active Employee Based on Hire/Term Date

[mdalton]

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.

[mdalton · Accepted solution]

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.