Power BI中用DAX循环计算考虑非工作日的任务结束日期

需求背景 #

需要基于任务估算时长计算准确的结束日期，核心逻辑：

• 以起始日期为基准，用估算时长/每日工作时长得到不含非工作日的初步结束日期；
• 统计该时间段内的员工缺勤、周末、公共假期，将这些非工作日天数加到结束日期；
• 重复检查新增时间段内的非工作日，直到无新增，得到最终结束日期。

现有代码仅完成单次非工作日计算，无法处理新增时间段内的非工作日，需完善迭代逻辑。

解决方案 #

DAX没有原生循环，但可以通过递归迭代实现收敛计算。以下分两步实现：

1. 定义辅助度量值：计算指定日期范围的总非工作日数

先统一封装非工作日的计算逻辑，方便后续复用：

总非工作日数 = 
VAR 起始日期 = MIN('任务表'[起始日期]) -- 替换为实际起始日期，或用TODAY()
VAR 结束日期 = MAX('任务表'[临时结束日期])
-- 计算周末天数
VAR 周末数 = CALCULATE(
    COUNTROWS('SHARED CalendarDay'),
    KEEPFILTERS('SHARED CalendarDay'[Weekday] >= 6),
    KEEPFILTERS('SHARED CalendarDay'[Date] >= 起始日期 && 'SHARED CalendarDay'[Date] <= 结束日期)
)
-- 计算公共假期天数（复用现有[SLOholidays]度量值）
VAR 公共假期数 = [SLOholidays]
-- 计算员工缺勤天数（缺勤小时数转换为天数）
VAR 缺勤总小时 = CALCULATE(
    SUM('GAM Absence'[Reported]) + SUM('GAM AbsenceRequest'[Reported]),
    KEEPFILTERS('SHARED CalendarDay'[Date] >= 起始日期 && 'SHARED CalendarDay'[Date] <= 结束日期)
)
VAR 缺勤天数 = CEILING(缺勤总小时 / HoursWorkingPerDay[HoursWorkingPerDay Value], 1)
RETURN 周末数 + 公共假期数 + 缺勤天数

2. 主度量值：迭代收敛计算最终结束日期

使用GENERATE实现递归迭代，直到新增非工作日数为0：

最终任务结束日期 = 
VAR 每日工作时长 = HoursWorkingPerDay[HoursWorkingPerDay Value]
VAR 剩余工作量 = [Remaining Effort Sum (h)]
VAR 起始日期 = TODAY() -- 替换为任务实际起始日期
-- 第一步：计算初步结束日期（仅考虑工作量，忽略非工作日）
VAR 初步结束日期 = 起始日期 + CEILING(剩余工作量 / 每日工作时长, 1)
-- 第二步：递归迭代，直到无新增非工作日
VAR 迭代序列 = 
    GENERATE(
        -- 初始化：首次计算的结束日期和对应非工作日数
        ROW("当前结束日期", 初步结束日期, "新增非工作日数", CALCULATE([总非工作日数], 'SHARED CalendarDay'[Date] >= 起始日期 && 'SHARED CalendarDay'[Date] <= 初步结束日期)),
        -- 递归生成后续迭代步骤
        VAR 上一次结束日期 = [当前结束日期]
        VAR 新增天数 = [新增非工作日数]
        RETURN
            IF(新增天数 > 0,
                ROW(
                    "当前结束日期", 上一次结束日期 + 新增天数,
                    "新增非工作日数", 
                        CALCULATE(
                            [总非工作日数],
                            'SHARED CalendarDay'[Date] > 上一次结束日期 && 'SHARED CalendarDay'[Date] <= 上一次结束日期 + 新增天数
                        )
                )
            )
    )
-- 取迭代序列的最后一个日期作为最终结果
VAR 最终日期 = MAXX(迭代序列, [当前结束日期])
RETURN 最终日期

关键说明 #

• 迭代逻辑：每次基于上一次的结束日期，计算新增时间段内的非工作日数，直到新增数为0（即新增时间段内没有非工作日）；
• 日期表依赖：确保SHARED CalendarDay是完整的日期维度表，包含Weekday字段和假期标记；
• 上下文适配：若需要按员工/任务维度计算，需确保度量值的上下文能正确关联到对应的员工缺勤数据和任务起始日期。

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