PowerBI DAX 基于项目与周起始表创建逐周预估工时表

核心逻辑 #

不需要写复杂的逐行遍历逻辑，用笛卡尔积匹配+条件筛选即可生成目标结构表，生成的表天然支持项目、时间维度的筛选联动，满足趋势图、日历视图的展示需求。

前置校验 #

先确认两个基础表的命名和字段符合预期：

• 周维度日期表命名为Week Calendar，包含日期类型字段Start of Week，计算规则为Start of Week = [Date] - WEEKDAY([Date],2) + 1，覆盖所有需要统计的时间范围
• 项目信息表命名为Project Info，包含4个核心字段：文本类型Project Name、日期类型Estimated Start Date、整数类型Duration in weeks、数值类型Hours Per Week

计算表DAX代码 #

直接在PowerBI中选择「新建表」，粘贴以下代码即可生成目标表：

Project Weekly Estimated Hours = 
VAR ProjectWithBaseWeek =
    ADDCOLUMNS(
        'Project Info',
        // 将项目预估开始日期对齐到周一起始的周维度，和日期表规则统一
        "Project Start Week", 'Project Info'[Estimated Start Date] - WEEKDAY('Project Info'[Estimated Start Date],2) + 1
    )
RETURN
FILTER(
    CROSSJOIN(
        SELECTCOLUMNS('Week Calendar', "Start of Week", 'Week Calendar'[Start of Week]),
        SELECTCOLUMNS(
            ProjectWithBaseWeek,
            "Project", 'Project Info'[Project Name],
            "Project Start Week", [Project Start Week],
            "Total Duration Days", 'Project Info'[Duration in weeks] * 7,
            "Weekly Input Hours", 'Project Info'[Hours Per Week]
        )
    ),
    // 筛选出落在项目周期覆盖范围内的周
    [Start of Week] >= [Project Start Week]
    && [Start of Week] < [Project Start Week] + [Total Duration Days]
)
// 只保留目标要求的三个字段
|> SELECTCOLUMNS(
    "Start of Week", [Start of Week],
    "Project", [Project],
    "Hours", [Weekly Input Hours]
)

兼容说明：如果使用2023年之前发布的旧版PowerBI Desktop（不支持|>管道符语法），请使用以下兼容版本代码：

Project Weekly Estimated Hours = 
VAR ProjectWithBaseWeek =
    ADDCOLUMNS(
        'Project Info',
        "Project Start Week", 'Project Info'[Estimated Start Date] - WEEKDAY('Project Info'[Estimated Start Date],2) + 1
    )
VAR MatchedWeekProject =
    FILTER(
        CROSSJOIN(
            SELECTCOLUMNS('Week Calendar', "Start of Week", 'Week Calendar'[Start of Week]),
            SELECTCOLUMNS(
                ProjectWithBaseWeek,
                "Project", 'Project Info'[Project Name],
                "Project Start Week", [Project Start Week],
                "Total Duration Days", 'Project Info'[Duration in weeks] * 7,
                "Weekly Input Hours", 'Project Info'[Hours Per Week]
            )
        ),
        [Start of Week] >= [Project Start Week]
        && [Start of Week] < [Project Start Week] + [Total Duration Days]
    )
RETURN
SELECTCOLUMNS(
    MatchedWeekProject,
    "Start of Week", [Start of Week],
    "Project", [Project],
    "Hours", [Weekly Input Hours]
)

后续配置要点 #

• 表生成后，建立两段关系：新表Start of Week字段关联Week Calendar表的同名字段（一对多、单方向筛选），新表Project字段关联Project Info表的Project Name字段（一对多、单方向筛选），即可自动打通全模型的筛选联动
• 制作周度工时趋势图时，直接将Start of Week拖入轴、对Hours字段求和作为值即可；制作日历视图时，将周起始日期关联到日粒度日期表，即可映射到对应自然日做热力展示
• 如果需要适配项目周中启动/收尾的非整周工时场景，只需要调整最后Hours字段的计算逻辑，按项目起止日期和当前周的重叠天数占比折算工时即可，当前代码默认覆盖到的周均按满周工时计算

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