Forum Discussion
Helper ICountIF 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.
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.
1 Reply
mdaltonI 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.