Forum Discussion

alexcatala's avatar
alexcatala
Icon for Helper IV rankHelper IV
4 years ago
Solved

BOP and EOP headcount

Hi, 

 

I am currently working on a formula to calculate the employee headcount at the beginning of the period (BOP) and End of the period (EOP)

 

The formulas for these are:

BoP Date = MIN ( d_DateTable[Date] )
EoP Date = Max ( d_DateTable[Date] )

 

I have found this formula which works for the current employees but I would like to filter by each period, BOP and EOP.

 

Current Employees = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]<=max(d_DateTable[Date]) && (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>max('d_DateTable'[Date]))),(f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))
 
Any suggestion to create two formulas, 1 for the BOP active employees and another for the EOP  active employees?
 
Kind regards,
 
Alex Catala
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    alexcatala , Please try

     

    Current Employees BOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]< Min(d_DateTable[Date]) &&
    (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>=Min('d_DateTable'[Date]))),
    (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

     

     

    Current Employees EOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]<=max(d_DateTable[Date]) &&
    (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>max('d_DateTable'[Date]))),
    (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    alexcatala , Please try

     

    Current Employees BOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]< Min(d_DateTable[Date]) &&
    (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>=Min('d_DateTable'[Date]))),
    (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))

     

     

    Current Employees EOP = CALCULATE(COUNTx(FILTER(f_Employees,f_Employees[EmpJoinDate]<=max(d_DateTable[Date]) &&
    (ISBLANK(f_Employees[EmpLeavDate]) || f_Employees[EmpLeavDate]>max('d_DateTable'[Date]))),
    (f_Employees[EmplCode])),CROSSFILTER(f_Employees[EmpJoinDate],'d_DateTable'[Date],None))