如何用DAX在Power BI中计算排除非工作时间的周期时长？

需求说明 #

计算每个ID从'Assigned'状态到'Completed'状态的周期时间，需排除周一至周五8:00-17:00之外的非工作时间及节假日。

现有代码问题分析 #

1. 第一段代码仅简单计算两个时间点的分钟差，完全未处理非工作时间，无法满足需求。
2. 第二段代码仅调整了当天的起止时间，未处理跨天场景，也未排除周末和节假日，且非工作时间计算逻辑存在错误（如公式中-2*60属于计算逻辑失误）。

正确实现方案 #

要实现该需求，核心是通过**日历表（Date Table）**预先标记工作日、工作时间范围，再通过DAX计算两个时间点之间的有效工作分钟数。

步骤1：创建日历表 #

先构建包含所有日期的日历表，标记工作日（排除周末），若有节假日需额外标记排除：

Date Table = 
VAR BaseDates = CALENDAR(MIN('Flat File Records'[Date]), MAX('Flat File Records'[Date]))
RETURN
ADDCOLUMNS(
    BaseDates,
    "IsWorkDay", IF(WEEKDAY([Date], 2) <= 5, 1, 0), -- 周一至周五标记为工作日
    "WorkStart", [Date] + TIME(8, 0, 0),
    "WorkEnd", [Date] + TIME(17, 0, 0),
    "WorkMinutesPerDay", (17-8)*60 -- 每日工作分钟数：9小时=540分钟
)

注：若需排除节假日，需添加IsHoliday列（手动维护或导入节假日数据），并将IsWorkDay修改为IF(WEEKDAY([Date],2)<=5 && [IsHoliday]=0,1,0)。

步骤2：编写DAX度量值计算有效工作时间 #

创建度量值Cycle Time (Business Hours)，分场景处理起止时间在同一天、跨多天的情况：

Cycle Time (Business Hours) = 
VAR AssignedDT = 
    CALCULATE(
        MINX(
            FILTER('Flat File Records', 'Flat File Records'[cr3d5_status] = "Assigned"),
            [Date] + TIMEVALUE([Time])
        ),
        ALLEXCEPT('Flat File Records', 'Flat File Records'[ID]) -- 按ID分组计算
    )
VAR CompletedDT = 
    CALCULATE(
        MAXX(
            FILTER('Flat File Records', 'Flat File Records'[cr3d5_status] = "Completed"),
            [Date] + TIMEVALUE([Time])
        ),
        ALLEXCEPT('Flat File Records', 'Flat File Records'[ID]) -- 按ID分组计算
    )
VAR StartDate = DATEVALUE(AssignedDT)
VAR EndDate = DATEVALUE(CompletedDT)
-- 计算起止日期之间的工作日总工作分钟（排除起止当天）
VAR WorkDaysBetween = 
    CALCULATE(
        SUM('Date Table'[WorkMinutesPerDay]),
        'Date Table'[Date] > StartDate,
        'Date Table'[Date] < EndDate,
        'Date Table'[IsWorkDay] = 1
    )
-- 计算分配当天的有效工作分钟
VAR StartDayMinutes = 
    IF(
        LOOKUPVALUE('Date Table'[IsWorkDay], 'Date Table'[Date], StartDate) = 1,
        MAX(0, DATEDIFF(AssignedDT, LOOKUPVALUE('Date Table'[WorkEnd], 'Date Table'[Date], StartDate), MINUTE)),
        0
    )
-- 计算完成当天的有效工作分钟
VAR EndDayMinutes = 
    IF(
        LOOKUPVALUE('Date Table'[IsWorkDay], 'Date Table'[Date], EndDate) = 1,
        MAX(0, DATEDIFF(LOOKUPVALUE('Date Table'[WorkStart], 'Date Table'[Date], EndDate), CompletedDT, MINUTE)),
        0
    )
-- 汇总总有效工作分钟数
VAR TotalBusinessMinutes = 
    IF(
        ISBLANK(AssignedDT) || ISBLANK(CompletedDT),
        BLANK(),
        IF(
            StartDate = EndDate,
            MAX(0, DATEDIFF(MAX(AssignedDT, LOOKUPVALUE('Date Table'[WorkStart], 'Date Table'[Date], StartDate)), MIN(CompletedDT, LOOKUPVALUE('Date Table'[WorkEnd], 'Date Table'[Date], EndDate)), MINUTE)),
            StartDayMinutes + WorkDaysBetween + EndDayMinutes
        )
    )
RETURN
TotalBusinessMinutes

代码说明 #

• AssignedDT/CompletedDT：按ID获取对应的分配、完成完整时间戳，确保计算同一ID的状态流转时间。
• WorkDaysBetween：统计起止日期之间所有工作日的总工作分钟数。
• StartDayMinutes：计算分配当天的有效工作时长（非工作日则为0）。
• EndDayMinutes：计算完成当天的有效工作时长（非工作日则为0）。
• 分同一天、跨天两种场景汇总最终有效工作分钟数，确保逻辑覆盖所有情况。

验证要点 #

• 确保日历表与业务表的日期字段关联正确。
• 节假日需在日历表的IsHoliday列标记为1，确保被排除。
• 测试跨周末、跨节假日的场景，验证计算结果是否符合预期。

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