基于PowerPivot模型计算员工当日含间隙工作时长的DAX需求

问题背景 #

你需要计算员工单日从最早工作开始时间到最晚工作结束时间的总时长（包含时段间的间隙），而非单纯累加各工作时段的时长。比如员工1在2023-7-3的工作时段覆盖08:07到08:34，实际总时长应为27分钟，但普通Excel透视表累加各时段仅得到21分钟，直接在透视表中取StartTime最小值与StopTime最大值相减的方法也未生效。

解决方案：DAX度量值 #

在PowerPivot模型中创建以下DAX度量值，可自动按「员工+日期」维度计算正确的总时长：

单日含间隙总时长(分钟) = 
VAR 最早开始时间 = MIN('工时表'[StartTime])
VAR 最晚结束时间 = MAX('工时表'[StopTime])
RETURN
DATEDIFF(最早开始时间, 最晚结束时间, MINUTE)

说明 #

1. 该度量值会根据透视表的行上下文（如当前行对应的员工和日期）自动筛选数据，取该分组下的最早开始时间与最晚结束时间
2. DATEDIFF函数直接计算两个时间点的分钟差，包含时段间的所有间隙时间
3. 基于PowerPivot模型的度量值支持数据频繁刷新，刷新后会自动重新计算结果

使用步骤 #

1. 在PowerPivot中导入你的工时数据表
2. 新建度量值，粘贴上述DAX代码，注意将'工时表'替换为你实际的表名
3. 在Excel透视表中，将员工、日期拖入行标签，把新建的度量值拖入值区域，即可得到正确的单日总时长

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