Power BI跨班次工时拆分：如何统计跨时段各班次工作时长？

班次规则 #

Morning   06.00 - 13.59
Afternoon 14.00 - 21.59
Night     22.00 - 05.59

现有数据集与DAX计算 #

现有TimesWork数据集包含StartTime（工时开始时间）和EndTime（工时结束时间）列，已创建以下DAX计算列：

Shift列（标记工时所属班次） #

Shift = 
IF(
    TIMEVALUE(TimesWork[Start]) >= TIMEVALUE("06:00:00") && TIMEVALUE(TimesWork[End]) <= TIMEVALUE("13:59:59"),
    "Morning",
    IF(
        TIMEVALUE(TimesWork[Start]) >= TIMEVALUE("14:00:00") && TIMEVALUE(TimesWork[End]) <= TIMEVALUE("21:59:59"),
        "Afternoon",
        IF(
            TIMEVALUE(TimesWork[Start]) >= TIMEVALUE("22:00:00") || TIMEVALUE(TimesWork[End]) <= TIMEVALUE("05:59:59"),
            "Night",
            BLANK()
        )
    )
)

Alert列（标记跨班次工时） #

Alert = 
IF(
    (TIMEVALUE(TimesWork[Start]) < TIMEVALUE("13:59") && TIMEVALUE(TimesWork[End]) > TIMEVALUE("14:00")) ||
    (TIMEVALUE(TIMEWORK[Start]) < TIMEVALUE("21:59") && TIMEVALUE(TIMEWORK[End]) > TIMEVALUE("22:00")) ||
    (TIMEVALUE(TIMEWORK[Start]) < TIMEVALUE("05:59") && TIMEVALUE(TIMEWORK[End]) > TIMEVALUE("06:00")),
    "Alert",
    BLANK()
)

核心需求 #

当Alert触发（即工时跨班次，例如13:56开始、14:15结束）时，拆分计算该工时在各班次的耗时分钟数，上述例子需在Morning Times列统计4分钟，Afternoon Times列统计15分钟。

通过创建三个独立的DAX计算列，分别统计每条工时在早班、中班、夜班的耗时分钟数，具体实现如下：

1. Morning Times（早班耗时分钟数） #

Morning Times = 
VAR StartTime = TIMEVALUE(TimesWork[StartTime])
VAR EndTime = TIMEVALUE(TimesWork[EndTime])
VAR MorningStart = TIMEVALUE("06:00:00")
VAR MorningEnd = TIMEVALUE("13:59:59")
VAR OverlapStart = MAX(StartTime, MorningStart)
VAR OverlapEnd = MIN(EndTime, MorningEnd)
VAR IsCrossDay = EndTime < StartTime
VAR CrossDayOverlap = 
    IF(
        IsCrossDay,
        IF(EndTime <= MorningEnd, EndTime - MorningStart, 0) + 
        IF(StartTime >= MorningStart, 1 - StartTime, 0),
        0
    )
VAR NormalOverlap = IF(OverlapEnd > OverlapStart, (OverlapEnd - OverlapStart)*1440, 0)
RETURN
MAX(NormalOverlap + CrossDayOverlap, 0)

2. Afternoon Times（中班耗时分钟数） #

Afternoon Times = 
VAR StartTime = TIMEVALUE(TimesWork[StartTime])
VAR EndTime = TIMEVALUE(TimesWork[EndTime])
VAR AfternoonStart = TIMEVALUE("14:00:00")
VAR AfternoonEnd = TIMEVALUE("21:59:59")
VAR OverlapStart = MAX(StartTime, AfternoonStart)
VAR OverlapEnd = MIN(EndTime, AfternoonEnd)
VAR IsCrossDay = EndTime < StartTime
VAR CrossDayOverlap = 
    IF(
        IsCrossDay,
        IF(EndTime <= AfternoonEnd, EndTime - AfternoonStart, 0) + 
        IF(StartTime >= AfternoonStart, 1 - StartTime, 0),
        0
    )
VAR NormalOverlap = IF(OverlapEnd > OverlapStart, (OverlapEnd - OverlapStart)*1440, 0)
RETURN
MAX(NormalOverlap + CrossDayOverlap, 0)

3. Night Times（夜班耗时分钟数） #

Night Times = 
VAR StartTime = TIMEVALUE(TimesWork[StartTime])
VAR EndTime = TIMEVALUE(TimesWork[EndTime])
VAR NightStart = TIMEVALUE("22:00:00")
VAR NightEnd = TIMEVALUE("05:59:59")
VAR IsCrossDay = EndTime < StartTime
VAR NormalOverlap = 
    IF(
        NOT IsCrossDay,
        IF(StartTime >= NightStart, MIN(EndTime, 1) - StartTime, 0),
        0
    )
VAR CrossDayOverlap = 
    IF(
        IsCrossDay,
        (1 - StartTime) + IF(EndTime <= NightEnd, EndTime, 0),
        0
    )
VAR NonCrossNightOverlap = 
    IF(
        NOT IsCrossDay && EndTime >= NightStart,
        EndTime - NightStart,
        0
    )
RETURN
MAX((NormalOverlap + CrossDayOverlap + NonCrossNightOverlap)*1440, 0)

逻辑说明 #

• 每个计算列先提取工时的起止时间及对应班次的时间范围
• 区分跨天工时（结束时间早于开始时间，如23:00到次日02:00）和非跨天工时两种场景
• 计算工时与对应班次的重叠时间段，将重叠时长转换为分钟数（1天=1440分钟）
• 最终返回非负值，避免出现无效的负时长结果

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