Excel PowerPivot中为员工连续缺勤行分配缺勤起始日期

需求说明 #

• 数据集结构：每位员工的每一天缺勤对应一行记录
• 目标：识别同一员工的连续缺勤时段（日期连续），将该时段的首个日期填充到该时段每一行的First Date Occurrence列
• 遇到的问题：尝试使用Earlier函数未得到预期结果

示例数据集 #

解决方案（DAX计算列） #

按以下步骤创建三个计算列，替换你的表名为实际表名：

1. 标记新缺勤时段的起始行
   创建计算列IsNewAbsence，判断当前行是否为新缺勤时段的开始：

IsNewAbsence = 
IF(
    AND(
        [Employee ID] = EARLIER([Employee ID]),
        [Time Off Date] = EARLIER([Time Off Date]) + 1
    ),
    0,
    1
)

逻辑：如果当前行和上一行是同一员工且日期连续，则不是新时段（标记0）；否则是新时段起始（标记1）。

2. 生成缺勤时段分组ID
   创建计算列AbsenceGroup，为每个员工的不同缺勤时段分配唯一分组号：

AbsenceGroup = 
CALCULATE(
    SUM([IsNewAbsence]),
    FILTER(
        ALL('你的表名'),
        [Employee ID] = EARLIER([Employee ID]) && [Time Off Date] <= EARLIER([Time Off Date])
    )
)

逻辑：按员工ID分组，对当前行及之前所有行的IsNewAbsence求和，得到对应时段的唯一分组ID。

3. 提取并填充时段首日
   创建计算列First Date Occurrence，提取对应分组的最小日期（即时段首日）：

First Date Occurrence = 
CALCULATE(
    MIN([Time Off Date]),
    FILTER(
        ALL('你的表名'),
        [Employee ID] = EARLIER([Employee ID]) && [AbsenceGroup] = EARLIER([AbsenceGroup])
    )
)

逻辑：按员工ID和分组ID筛选，取该分组下的最小Time Off Date，即为该缺勤时段的首日。

结果验证 #

按上述步骤处理后，示例数据集的First Date Occurrence列会填充为：

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