DAX度量仅单次统计EmployeeID，求月度员工人数计算方案

问题背景 #

需基于员工表的Hire Date（雇佣日期）、Termination Date（离职日期）、Transfer In Date（转入日期）字段，在PowerPivot中编写DAX度量，实现以下统计：

• 每月在职员工数
• 每月新入职员工数
• 每月转入员工数
• 每月离职员工数

当前ActiveEmployees度量存在问题：仅在员工入职/转入当月统计该员工，后续在职月份未被计入，原代码如下：

ActiveEmployees:=
CALCULATE(
 COUNTROWS(TableName),
 FILTER(
  TableName,
  (  
   NOT(ISBLANK([HireDate]) && [HireDate] < MAX(DimDate[Date])
  )
  &&
  (
   ISBLANK([TransferDate]) || [TransferDate] < MAX(DimDate[Date])
  )
  &&
  (
   ISBLANK([TermDate]) || [TermDate] > MAX(DimDate[Date])
  )
 )
)

问题原因 #

原代码直接筛选员工表，结合MAX(DimDate[Date])的逻辑仅判断员工是否在当前日期上下文的最后一天在职，但未遍历每个员工的全周期在职月份。需改为针对每个员工ID，判断其是否在当前统计月份内处于在职状态。

修正后的DAX度量 #

1. 每月在职员工数 #

ActiveEmployees := 
VAR CurrentMonthEnd = EOMONTH(MAX(DimDate[Date]), 0)
VAR CurrentMonthStart = STARTOFMONTH(DimDate[Date])
RETURN
SUMX(
    VALUES(TableName[EmployeeID]),
    VAR EmployeeHireDate = CALCULATE(MAX(TableName[HireDate]))
    VAR EmployeeTransferDate = CALCULATE(MAX(TableName[TransferDate]))
    VAR EmployeeTermDate = CALCULATE(MAX(TableName[TermDate]))
    VAR EffectiveStartDate = IF(ISBLANK(EmployeeTransferDate), EmployeeHireDate, MAX(EmployeeHireDate, EmployeeTransferDate))
    VAR IsActive = 
        AND(
            EffectiveStartDate <= CurrentMonthEnd,
            OR(ISBLANK(EmployeeTermDate), EmployeeTermDate > CurrentMonthStart)
        )
    RETURN
    IF(IsActive, 1, 0)
)

逻辑说明：

• 先获取当前统计月份的起止日期
• 遍历每个唯一员工ID，计算其有效入职日期（取雇佣日期和转入日期的较晚者）
• 判断该员工的有效起始日期是否早于等于当月月末，且离职日期（若有）晚于当月月初，满足则计为1，最后求和得到在职人数

2. 每月新入职员工数 #

NewHires := 
CALCULATE(
    DISTINCTCOUNT(TableName[EmployeeID]),
    DATESBETWEEN(
        DimDate[Date],
        STARTOFMONTH(DimDate[Date]),
        EOMONTH(DimDate[Date], 0)
    ),
    NOT(ISBLANK(TableName[HireDate])),
    ISBLANK(TableName[TransferDate]) -- 若转入员工不算新入职则保留，否则移除该条件
)

3. 每月转入员工数 #

TransfersIn := 
CALCULATE(
    DISTINCTCOUNT(TableName[EmployeeID]),
    DATESBETWEEN(
        DimDate[Date],
        STARTOFMONTH(DimDate[Date]),
        EOMONTH(DimDate[Date], 0)
    ),
    NOT(ISBLANK(TableName[TransferDate]))
)

4. 每月离职员工数 #

Terminations := 
CALCULATE(
    DISTINCTCOUNT(TableName[EmployeeID]),
    DATESBETWEEN(
        DimDate[Date],
        STARTOFMONTH(DimDate[Date]),
        EOMONTH(DimDate[Date], 0)
    ),
    NOT(ISBLANK(TableName[TermDate]))
)

内容的提问来源于stack exchange，提问作者rowneil10