如何在Power BI中分析Azure DevOps的工作项历史数据

• 保留WorkItemRevision表核心字段：WorkItemID、RevisionNumber、RevisedDate、工作量字段（根据实际配置替换，如Effort/StoryPoints）、State、IterationPath等业务所需字段
• 构建独立的非关联日期维度表Date，表内提前计算好日期对应的周、冲刺、PI归属字段，支持多维度统计
• 提前为WorkItemRevision表的WorkItemID、RevisedDate字段建立索引，降低DAX查询开销

1. 总工作量（支持日/周/冲刺/PI维度） #

该度量值会自动适配当前筛选上下文的时间范围，取时间范围最后一天所有工作项最新修订版本的工作量总和：

总工作量 = 
VAR MaxSelectedDate = MAX('Date'[Date])
VAR LastRevisionPerWI = 
    ADDCOLUMNS(
        VALUES('WorkItemRevision'[WorkItemID]),
        "LastRevDate", CALCULATE(MAX('WorkItemRevision'[RevisedDate]), 'WorkItemRevision'[RevisedDate] <= MaxSelectedDate)
    )
VAR Result = 
    CALCULATE(
        SUM('WorkItemRevision'[Effort]), // 替换为实际的工作量字段名
        TREATAS(LastRevisionPerWI, 'WorkItemRevision'[WorkItemID], 'WorkItemRevision'[RevisedDate])
    )
RETURN
    Result

2. 已完成工作量（支持日/周/冲刺/PI维度） #

在总工作量逻辑基础上增加状态筛选，可根据实际的工作项完成状态值调整筛选条件：

已完成工作量 = 
VAR MaxSelectedDate = MAX('Date'[Date])
VAR LastRevisionPerWI = 
    ADDCOLUMNS(
        VALUES('WorkItemRevision'[WorkItemID]),
        "LastRevDate", CALCULATE(MAX('WorkItemRevision'[RevisedDate]), 'WorkItemRevision'[RevisedDate] <= MaxSelectedDate)
    )
VAR Result = 
    CALCULATE(
        SUM('WorkItemRevision'[Effort]), // 替换为实际的工作量字段名
        TREATAS(LastRevisionPerWI, 'WorkItemRevision'[WorkItemID], 'WorkItemRevision'[RevisedDate]),
        'WorkItemRevision'[State] IN {"已完成", "Closed", "Resolved"} // 替换为实际的完成状态枚举值
    )
RETURN
    Result

3. 冲刺周期内移入冲刺的工作项数量 #

统计时间范围内（一般用于冲刺维度筛选），工作项迭代路径从非当前冲刺修改为当前冲刺的去重工作项数：

冲刺移入工作项数 = 
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
VAR CurrentIteration = SELECTEDVALUE('Date'[IterationPath]) // 替换为日期表中冲刺路径的字段名
VAR WIChangedToCurrent = 
    CALCULATETABLE(
        VALUES('WorkItemRevision'[WorkItemID]),
        'WorkItemRevision'[RevisedDate] >= MinDate,
        'WorkItemRevision'[RevisedDate] <= MaxDate,
        'WorkItemRevision'[IterationPath] = CurrentIteration,
        CALCULATE(
            VALUES('WorkItemRevision'[IterationPath]),
            OFFSET(-1, ORDERBY('WorkItemRevision'[RevisionNumber], ASC), , PARTITIONBY('WorkItemRevision'[WorkItemID]))
        ) <> CurrentIteration
    )
RETURN
    COUNTROWS(WIChangedToCurrent) + 0

4. 冲刺周期内移出冲刺的工作项数量 #

统计时间范围内，工作项迭代路径从当前冲刺修改为非当前冲刺的去重工作项数：

冲刺移出工作项数 = 
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
VAR CurrentIteration = SELECTEDVALUE('Date'[IterationPath]) // 替换为日期表中冲刺路径的字段名
VAR WIChangedFromCurrent = 
    CALCULATETABLE(
        VALUES('WorkItemRevision'[WorkItemID]),
        'WorkItemRevision'[RevisedDate] >= MinDate,
        'WorkItemRevision'[RevisedDate] <= MaxDate,
        'WorkItemRevision'[IterationPath] <> CurrentIteration,
        CALCULATE(
            VALUES('WorkItemRevision'[IterationPath]),
            OFFSET(-1, ORDERBY('WorkItemRevision'[RevisionNumber], ASC), , PARTITIONBY('WorkItemRevision'[WorkItemID]))
        ) = CurrentIteration
    )
RETURN
    COUNTROWS(WIChangedFromCurrent) + 0

• 数据量过大时可提前在SQL层对WorkItemRevision按年/季度做分区，DAX查询性能可提升30%以上
• 无需全量拉取过久的历史修订数据，可根据业务需求过滤3年以上的历史数据进一步提升性能
• 可通过DAX Studio优化慢查询，识别不必要的计算逻辑做针对性调整

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