Power BI中计算工单平均工时求助（含工作日时长规则）

前置准备：创建日期表 #

首先需要建立独立的日期表来逐天统计工时，DAX代码如下：

日期表 = CALENDAR(MIN('工单表'[created]), MAX('工单表'[close]))

在日期表中添加两个计算列，用于区分工作日类型和单日基础工时：

工作日类型 = 
SWITCH(
    WEEKDAY('日期表'[Date], 2), // 参数2表示周一为1，周日为7
    6, "周六",
    7, "周日",
    "工作日"
)

单日基础工时 = 
SWITCH(
    '日期表'[工作日类型],
    "工作日", 8,
    "周六", 4,
    0 // 周日不计入工时
)

方案1：按完整日期统计有效工时（只要工单当天存续即算全额工时） #

1. 单工单总有效工时度量值 #

单工单总有效工时 = 
VAR 工单开始日期 = DATE(YEAR(MIN('工单表'[created])), MONTH(MIN('工单表'[created])), DAY(MIN('工单表'[created])))
VAR 工单结束日期 = DATE(YEAR(MAX('工单表'[close])), MONTH(MAX('工单表'[close])), DAY(MAX('工单表'[close])))
VAR 工单日期范围 = DATESBETWEEN('日期表'[Date], 工单开始日期, 工单结束日期)
RETURN
SUMX(工单日期范围, '日期表'[单日基础工时])

2. 平均工单有效工时度量值 #

平均工单有效工时 = 
DIVIDE(
    SUMX('工单表', [单工单总有效工时]),
    DISTINCTCOUNT('工单表'[ID_Ticket]),
    0 // 若无工单返回0，避免报错
)

方案2：按实际时间段统计有效工时（精确到小时） #

如果需要计算工单在当天工作时段内的实际耗时，先创建以下度量值：

1. 单日实际有效工时度量值 #

单日实际有效工时 = 
VAR 当前日期 = '日期表'[Date]
VAR 工单开始时间 = MIN('工单表'[created])
VAR 工单结束时间 = MAX('工单表'[close])
VAR 当日工作开始时间 = 
    SWITCH(
        '日期表'[工作日类型],
        "工作日", 当前日期 + TIME(9, 0, 0),
        "周六", 当前日期 + TIME(9, 0, 0),
        BLANK()
    )
VAR 当日工作结束时间 = 
    SWITCH(
        '日期表'[工作日类型],
        "工作日", 当前日期 + TIME(17, 0, 0),
        "周六", 当前日期 + TIME(13, 0, 0),
        BLANK()
    )
VAR 实际开始时间 = MAX(工单开始时间, 当日工作开始时间)
VAR 实际结束时间 = MIN(工单结束时间, 当日工作结束时间)
RETURN
IF(实际开始时间 <= 实际结束时间, DATEDIFF(实际开始时间, 实际结束时间, MINUTE)/60, 0)

2. 单工单总实际有效工时度量值 #

单工单总实际有效工时 = 
VAR 工单开始日期 = DATE(YEAR(MIN('工单表'[created])), MONTH(MIN('工单表'[created])), DAY(MIN('工单表'[created])))
VAR 工单结束日期 = DATE(YEAR(MAX('工单表'[close])), MONTH(MAX('工单表'[close])), DAY(MAX('工单表'[close])))
VAR 工单日期范围 = DATESBETWEEN('日期表'[Date], 工单开始日期, 工单结束日期)
RETURN
SUMX(工单日期范围, [单日实际有效工时])

3. 平均工单实际有效工时度量值 #

平均工单实际有效工时 = 
DIVIDE(
    SUMX('工单表', [单工单总实际有效工时]),
    DISTINCTCOUNT('工单表'[ID_Ticket]),
    0
)

注意事项 #

• 若存在未关闭的工单（close字段为空），可在工单结束日期变量中添加判断，替换为当前日期：

  VAR 工单结束日期 = 
      VAR 原始结束时间 = MAX('工单表'[close])
      RETURN DATE(YEAR(IF(原始结束时间=BLANK(), TODAY(), 原始结束时间)), MONTH(IF(原始结束时间=BLANK(), TODAY(), 原始结束时间)), DAY(IF(原始结束时间=BLANK(), TODAY(), 原始结束时间)))
  

• 日期表无需与工单表建立关系，度量值会通过SUMX和DATESBETWEEN自行遍历对应日期范围。

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