将状态时间度量值添加到 Power BI 报表

项目
03/06/2024

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019

工作项在特定工作流状态或一系列状态中花费的时间是了解效率的一个重要方面。 “周期时间”和“提前期分析”小组件提供一些状态时间度量值。但是，这些小组件可能没有所需的详细信息级别。

本文提供了使用数据分析表达式（DAX）评估工作项在状态组合中花费的时间的食谱。具体而言，你将了解如何将以下度量值和计算列添加到 Power BI 报表，并使用它们生成各种趋势图。除列出的第一个字段外，所有字段都是计算列。

计数	说明
工作项计数 (度量)	基于工作项的最后一天条目计算不同工作项的计数
状态排序顺序	添加一个列，用于根据状态类别序列对工作流状态进行排序
上一个日期	添加一个列，该列基于 Date 列计算上一个日期
日期差异（以天为单位）	添加一个列，用于计算 Date 和 Date Previous 列之间的天数
处于状态的最后一天	添加一个列，用于确定日期值是否是工作项处于状态的最后一天
状态时间（以天为单位）	添加一个列，用于计算工作项在每个州花费的天数
上一个状态	添加一个列，用于标识数据表中每一行的上一个状态
状态已更改	添加一个列，用于确定工作项从一个州转换到另一个州的日期
状态流	添加一个列，用于说明工作项从一个状态转换到另一个状态时的状态流
状态更改计数	添加一个列，用于计算工作项从一个状态转换到另一个状态的次数
状态更改计数 - 首次完成	添加一个列，该列确定工作项首次转换为“已完成”状态的次数。换句话说，当它从任何其他状态移动到“已完成”状态时。
状态更改计数 - 上次建议	添加一个列，用于确定工作项在转换为更高状态后之前是否处于“建议 ”状态
状态重启时间（天）	添加一个列，用于计算工作项处于重启状态的天数
状态返工时间（以天为单位）	添加一个列，用于计算工作项在“已完成”以外的状态中花费的天数

重要

根据本文所示的示例添加计算列或度量值时，请将 视图名称 替换为 Analytics 视图或数据表的表名。例如，将 “视图名称” 替换为 “活动 Bug”。
分析不支持日内修订。这些示例在引用 Analytics 视图时使用 “每日 ”间隔时精度最高。
所有日内或周期内 (每周/每月) 修订将被计算忽略。这可能会导致特定方案（例如，当工作项“正在进行”少于一天时显示没有时间“正在进行”的工作项）产生意外结果。
尽可能使用 Power BI 默认聚合，而不是生成度量值。
某些计算包括 +0 ，以确保每一行都包含数值，而不是 BLANK。
可能需要根据项目使用的工作流状态修改一些计算列定义。例如，如果项目使用 New、 Active 和 Closed 代替 Proposed、 In Progress 和 Completed。
本文中提到的 Date 列不是 Azure DevOps 中的本机列;它是在 PowerBI 中创建的派生列，用于促进“状态中的时间”报表。可以使用与日期相关的现有列（如“已更改日期”或“状态已更改日期”）生成此列。

先决条件

Access：至少是具有基本访问权限的项目的成员。
权限： 默认情况下，项目成员有权查询 Analytics 和创建视图。
有关服务和功能启用和常规数据跟踪活动的其他先决条件的详细信息，请参阅访问 Analytics 的权限和先决条件。

注意

若要执行本文中所述的所有状态时间度量值，请确保在分析视图、Power Query或 OData 查询中包含以下字段：除默认字段外，“创建日期”和“状态类别”：“区域路径”、“分配到”、“迭代路径”、“状态”、“标题”、“工作项 ID”和“工作项类型”。

此外，请考虑使用基于每日粒度的分析视图。本文中的示例基于基于自定义分析视图在 Power BI 中创建活动 Bug 报表中定义的活动 Bug 分析视图，但选择了 60 天的 历史记录 和每日粒度除外。此外，确定是要查看已完成的工作项还是已关闭的工作项。

添加工作项计数度量值

为了简化报表的快速生成，我们设计了用于 Power BI 中的默认聚合的分析视图。为了说明默认聚合与度量值之间的差异，我们从简单的工作项计数度量值开始。

将分析视图加载到Power BI Desktop。有关详细信息，请参阅使用 Power BI 数据连接器进行连接、连接到分析视图。
选择数据表，然后从功能区的“ 表工具 ”选项卡的“ 计算 ”部分，选择“ 新建度量值”。
将默认文本替换为以下代码，然后选择复选标记。
```
Work Items Count=CALCULATE(COUNTROWS ('View Name'),LASTDATE ('View Name'[Date]))
```
工作项计数度量值使用 CALCULATECOUNTROWS、和 LASTDATE DAX 函数，本文稍后将更详细地介绍这些函数。

注意

请记得将 “视图名称” 替换为“分析”视图的表名。例如，此处我们将 视图名称 替换为 活动 bug。

度量值与计算列有何不同

度量值始终计算整个表，其中计算列特定于单个行。有关详细信息，请参阅 DAX 中的计算列和度量值。

基于工作项 ID 将工作项计数度量值与默认计数聚合进行比较。通过将卡片视觉对象和 工作项计数 度量值添加到第一张卡，将 工作项 ID 属性添加到第二张卡来创建下图。

Power BI 报表页的屏幕截图，其中两张卡片显示了“工作项计数”度量值和“工作项 ID”属性。

若要使用默认聚合获取正确的计数，请应用筛选器 “Current ”等于“True”。将筛选器应用于默认聚合的这种模式是本文中提供的许多示例的基础。

Power BI 报表页的屏幕截图，其中筛选了工作项 ID 计数。

添加状态排序顺序

默认情况下，Power BI 在可视化效果中按字母顺序显示状态。如果要可视化状态中的时间，并且 “建议” 显示在 “正在进行”之后，可能会产生误导。以下步骤有助于解决此问题。

验证“ 状态类别” 字段是否包含在“分析”视图中。此字段包含在所有默认共享视图中。
选择数据表，然后从功能区的“ 表工具 ”选项卡的“ 计算 ”部分，选择“ 新建列”。
将默认文本替换为以下代码，然后选择复选标记。
```
State Sort Order =  
SWITCH (  
    'View Name'[State Category],  
    "Proposed", 1,  
    "InProgress", 2,  
    "Resolved", 3,  
    4  
)  
```
请参阅以下示例：

注意

如果需要的粒度高于 State Category 提供的粒度，则可能需要修改定义。 状态类别 在所有工作项类型之间提供正确的排序，而不考虑任何状态自定义。
打开 “数据” 视图并选择“ 状态” 列。
在“ 列工具 ”选项卡中，选择“ 按列排序 ”，然后选择“ 状态排序顺序 ”字段。

添加上一个日期

计算状态时间的下一步需要映射数据集中每行数据的上一个间隔 (日、周、月) 。它是使用计算列的简单计算。通常，将定义此列，如下所示。

Date Previous  = 
PREVIOUSDAY ( 'View Name'[Date] )

但是，此方法有两个主要问题：

它仅适用于每日时间段。
它不处理数据中的间隙。例如，如果在项目之间移动工作项。

若要解决这些问题，计算列应通过扫描 “日期 ”字段来查找前一天。

若要添加 Date Previous 计算列，请从“ 表工具 ”选项卡中选择“ 新建列” ，然后将默认文本替换为以下代码并选择复选标记。

Date Previous =
CALCULATE (
    MAX ( 'View Name'[Date] ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] < EARLIER ( 'View Name'[Date] )
)

Date Previous 计算列使用三个 DAX 函数、ALLEXCEPT、和 EARLIER，本文稍后会更详细地介绍这些函数MAX。由于计算列，因此它针对表中的每一行运行，每次运行时，它都有该特定行的上下文。

Power BI 表图表和“上一日期”的“可视化效果”选项卡的屏幕截图。

提示

从“ 日期 ”和“ 上一日期” 字段的上下文菜单中，选择“ 日期 (而不是 日期层次结构) ”以查看这些字段的单个日期。

添加日期差异（以天为单位）

Date Previous 计算每行上一个日期和当前日期之间的差值。使用 日期差异（以天为单位），我们计算每个时间段之间的天数计数。对于每日快照中的大多数行，该值等于 1。但是，对于数据集中存在间隙的许多工作项，该值大于 1。

重要

要求已将 “上一个日期 ”计算列添加到表中。

请务必考虑数据集的第一天，其中 “上一个日期” 为空。在此示例中，我们为该行提供标准值 1 以保持计算一致。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

Date Diff in Days =
IF (
    ISBLANK ( 'View Name'[Date Previous] ),
    1,
    DATEDIFF (
        'View Name'[Date Previous],
        'View Name'[Date],
        DAY
    )
)

此计算列使用ISBLANK本文稍后介绍的和 DATEDIFF DAX 函数。

添加为状态的最后一天

在下一步中，我们计算给定行是否表示特定工作项处于状态的最后一天。它支持 Power BI 中的默认聚合，我们将在下一部分中添加 “状态时间（天 ）”列。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

Is Last Day in State = 
ISBLANK (CALCULATE (
    COUNTROWS ( 'View Name' ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] > EARLIER ( 'View Name'[Date] ),
        'View Name'[State] = EARLIER ( 'View Name'[State] )
))

添加状态时间（以天为单位）

现在，可以通过对每个工作项的 日期差异（以天为单位 ）求和来计算工作项在特定状态下花费的时间。此计算包括在特定状态下花费的所有时间，即使它多次在状态之间切换。可以使用 “日期 ”或“状态的最后一天”将每一行评估为趋势，或者使用“状态 为最后一天”的最新信息。

重要

要求已将 Date Diff in Days 和 Is Last Day in State 计算列添加到表中。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Time in Days = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State] = EARLIER ( 'View Name'[State] )
) + 0

创建基于状态时间（以天为单位）的堆积柱形图

为了演示“ 状态时间（以天为单位 ）”列，将创建以下堆积柱形图。第一个图表显示一段时间内每个状态的工作项计数。

Power BI 堆积柱形图和可视化选项卡的屏幕截图，其中按日期和状态演示了工作项标题的计数。

第二个图表演示了活动工作项处于特定状态的平均天数趋势。

Power BI 堆积柱形图和“可视化效果”选项卡的屏幕截图，其中按日期说明了工作项处于状态的平均天数。

添加状态时间（以天为单位） - 最新 (是状态) 的最后一天

在计算表中每个工作项的处于状态的时间或按字段（如 “区域路径”）进行筛选时，请勿在聚合中使用 “状态时间（以天为单位 ）”列。聚合使用工作项处于状态的每一天的值。例如，如果某个工作项在星期一处于 “正在进行 ”状态，并在周四移动到 “已完成 ”，则状态为三天，但状态时间（以天为单位）列的总和为六天， 1+2+3这是不正确的。

添加状态时间（天 - 最新）。

若要解决此问题，请使用 “天 ”中的状态时间，并应用筛选器 “处于状态 的最后一天”等于“True”。它消除了趋势所需的所有历史数据，而是只关注每个状态的最新值。

使用“状态时间”（以天为单位）并应用筛选器“状态的最后一天”等于“True”。

添加状态时间（以天为单位 - 正在进行中）

在前面的示例中， 仅当工作项处于该特定状态时，才会计算给定工作项的状态时间（天 ）。如果目标是使给定工作项的状态时间连续计入平均值，则必须更改计算。例如，如果要跟踪“正在进行”状态，请添加 “状态时间（以天为单位） - 正在进行” 计算列。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Time in Days - In Progress = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
   'View Name'[Date] <= EARLIER('View Name'[Date]),
   'View Name'[State] = "In Progress"
) + 0

注意

可能需要根据项目使用的工作流状态修改定义。例如，本文示例中使用的项目使用“正在进行”工作流状态，但敏捷、Scrum 和 CMMI 进程通常使用“活动”或“已提交”状态来表示正在进行的工作。有关概述，请参阅工作流状态和状态类别。

下图显示了考虑每个现有工作项的所有状态时间的效果， (左) 显示，而只考虑给定日期处于特定状态的工作项， (右) 显示。

状态平均时间的趋势比较

多个状态的状态时间（以天为单位）趋势

还可以使用“连续”模式跨多个状态分析性能。但是，此方法仅适用于趋势图。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Time in Days - Working States = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
   'View Name'[Date] <= EARLIER('View Name'[Date]),
   'View Name'[State] IN { "Committed", "In Progress" }
) + 0

注意

可能需要根据项目使用的工作流状态修改定义。例如，如果项目使用“Active”代替“Committed”或“Proposed”。

左侧的图表显示组合平均值，右侧显示每个单独的状态。

多个状态的趋势比较

获取多个状态的状态时间（以天为单位- 最新）

创建趋势时，请使用 “状态时间”（天 - 最新 计算）列。通过对状态进行筛选，“状态时间”列和“状态中的最后一天”提供了一种简单方法，用于获取在一组状态中花费的任何工作项或工作项组的总时间。

添加上一个状态

“ 日期上一个 ”计算列还可用于查找过去值，例如每个工作项的上一个状态。

重要

要求已将 “上一个日期 ”计算列添加到表中。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Previous =
LOOKUPVALUE (
    'View Name'[State],
    'View Name'[Work Item Id], 'View Name'[Work Item Id],
    'View Name'[Date], 'View Name'[Date Previous]
)

此计算列使用 LOOKUPVALUE本文稍后介绍的。

第一个 LOOKUPVALUE 参数 'View Name'[State]指定返回 [State] 的值。

下一个参数 'View Name'[Work Item Id], 'View Name'[Work Item Id]指定仅考虑具有匹配工作项 ID 的行作为当前行。

最后一个参数 'View Name'[Date], 'View Name'[Date Previous]指定返回的行的日期必须具有与当前行的 [上一个日期] 匹配的 [Date]。在快照中，只有一行符合此条件。

添加状态已更改

使用 “状态上一列 ”，我们可以标记发生状态转换的每个工作项的行。 阶段更改计算列有两个特殊注意事项：

*State Previous 的空白值，我们设置为工作项的创建日期
创建工作项被视为状态转换

重要

要求已将 “上一个状态” 计算列添加到表中。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Changed =
IF (
    ISBLANK ( 'View Name'[State Previous] ),
    'View Name'[Created Date].[Date] = 'View Name'[Date],
    'View Name'[State Previous] <> 'View Name'[State]
)

计算列是一个布尔值，用于标识行是否为状态转换。通过使用 Not Equal To 运算符，可以正确捕获先前状态与当前状态不匹配的行，这意味着比较将按预期返回 True。

添加状态流

使用 State Previous 和 State Changed 计算列，可以创建一个列来说明给定工作项 的状态流 。对于本文而言，创建此列是可选的。

重要

要求已将 “上一个状态” 和 “状态已更改” 计算列添加到表中。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码并选择复选标记。

State Flow = 
IF([State Changed], [State Previous], [State]) & " => " & [State]

添加状态更改计数

当我们进入更复杂的度量值时，我们需要具有状态更改总数的表示形式，以比较给定工作项的数据行。我们通过添加 状态更改计数 计算列来获取表示形式。

重要

要求已将 State Changed 计算列添加到表中。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码，然后选择复选标记。

State Change Count = 
CALCULATE (
    COUNTROWS ( 'View Name' ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State Changed]
) + 0

添加状态更改计数 - 上次建议和状态重启时间（以天为单位）

状态重启时间（以天 为单位）是一个相当复杂的计算。第一步是查找工作项最后一次处于建议状态的时间。添加 “状态更改计数 - 上次建议的 计算”列。

注意

可能需要根据项目使用的工作流状态修改以下定义。例如，如果项目使用“New”代替“Proposed”。

在“建模”选项卡中，选择“新建”列，然后将默认文本替换为以下代码，然后选择复选标记。

State Change Count - Last Proposed = 
CALCULATE (
    MAX ( 'View Name'[State Change Count] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State] = "Proposed"
)

然后，进一步回顾过去，看看在此建议状态之前是否有一些活动状态。最后，总结工作项在上次建议之前处于活动状态的所有天数。

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码，然后选择复选标记。

State Restart Time in Days = 
CALCULATE (
    SUM ( 'View Name'[Date Diff in Days] ),
    ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
    'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
    'View Name'[State Change Count] < EARLIER('View Name'[State Change Count - Last Proposed] ),
    'View Name'[State] <"Proposed"
) + 0

由于每行数据 的状态重启时间（以天为单位 ）已更新，因此可以创建一个趋势来评估跨特定冲刺的返工，或使用“ 是当前”检查单个工作项的返工。

添加状态返工时间（以天为单位）

与 状态重启时间（以天为单位）类似， “状态重置工时”在“天” 中查找工作项首次处于“已完成”状态类别中。在该时间之后，每天工作项花费在“已完成”以外的状态时，都计为返工。

创建“状态更改计数 - 首次完成”列。此列跟踪工作项从任何其他状态转换为已完成状态的次数。

State Change Count - First Completed =
VAR CompletedState = "Completed"
RETURN
CALCULATE(
   COUNTROWS('YourTable'),
   FILTER(
       'YourTable',
       'YourTable'[State] = CompletedState
       && 'YourTable'[State Change Date] = MIN('YourTable'[State Change Date])
   )
)

在“ 建模 ”选项卡中，选择“ 新建列 ”，然后将默认文本替换为以下代码，然后选择复选标记。

State Rework Time in Days = 
IF (
    ISBLANK ( 'View Name'[State Change Count - First Completed] ),
    0,
    CALCULATE (
        SUM ( 'View Name'[Date Diff in Days] ),
        ALLEXCEPT ( 'View Name', 'View Name'[Work Item Id] ),
        'View Name'[Date] <= EARLIER ( 'View Name'[Date] ),
        'View Name'[State Change Count] <= EARLIER ( 'View Name'[State Change Count - First Completed] ),
        'View Name'[State] IN {"Completed", "Closed", "Cut" } = FALSE()
    ) + 0
)

注意

可能需要根据项目使用的工作流状态修改以前的定义。例如，如果项目使用 Done 代替 Closed，依此等。

DAX 函数

本节中提供了用于创建本文中添加的计算列和度量值的 DAX 函数的其他信息。另请参阅 DAX、时间智能函数。

函数	说明
`ALLEXCEPT`	删除表中的所有上下文筛选器，但应用于指定列的筛选器除外。实质上， `ALLEXCEPT ('View Name'', 'View Name'[Work Item Id])` 会将表中的行减少到仅与当前行共享相同工作项 ID 的行。
`CALCULATE`	此函数是几乎所有示例的基础。基本结构是一个表达式，后跟应用于表达式的一系列筛选器。
`COUNTROWS`	此函数 `COUNTROWS ( 'View Name' )`只是计算应用筛选器后剩余的行数。
`DATEDIFF`	返回两个日期之间跨越的间隔边界的计数。 `DATEDIFF` 从 “日期 ”中减去 “上一个日期 ”，以确定它们之间的天数。
`EARLIER`	返回所述列的外部计算传递中指定列的当前值。例如， `'View Name'[Date] < EARLIER ( 'View Name'[Date] )` 进一步将数据集减少为仅那些使用 `EARLIER` 函数引用的当前行的日期之前发生的行。 `EARLIER` 不引用以前的日期;它专门定义计算列的行上下文。
`ISBLANK`	检查值是否为空白，并返回 TRUE 或 FALSE。 `ISBLANK` 计算当前行以确定 “上一个日期”是否具有值。否则，If 语句将 Date Diff in Days 设置为 1。
`LASTDATE`	我们将筛选器应用于 `LASTDATE` 表达式（例如 `LASTDATE ( 'View Name'[Date] )`），以查找表中所有行的最新日期，并消除不共享同一日期的行。对于分析视图生成的快照表，此筛选器可以有效地选取所选时间段的最后一天。
`LOOKUPVALUE`	在“result_columnName”中，为满足“search_columnName”和“search_value”指定的所有条件的行返回值。
`MAX`	返回列中或两个标量表达式之间的最大数字值。应用 `MAX ( 'View Name'[Date] )`后，确定应用所有筛选器后的最新日期。

通过

将状态时间度量值添加到 Power BI 报表

先决条件

添加工作项计数度量值

度量值与计算列有何不同

添加状态排序顺序

添加上一个日期

添加日期差异（以天为单位）

添加为状态的最后一天

添加状态时间（以天为单位）

创建基于状态时间（以天为单位）的堆积柱形图

添加状态时间（以天为单位） - 最新 (是状态) 的最后一天

添加状态时间（以天为单位 - 正在进行中）

多个状态的状态时间（以天为单位）趋势

获取多个状态的状态时间（以天为单位- 最新）

添加上一个状态

添加状态已更改

添加状态流

添加状态更改计数

添加状态更改计数 - 上次建议和状态重启时间（以天为单位）

添加状态返工时间（以天为单位）

DAX 函数

反馈

其他资源

通过

将状态时间度量值添加到 Power BI 报表

先决条件

添加工作项计数度量值

度量值与计算列有何不同

添加状态排序顺序

添加上一个日期

添加日期差异（以天为单位）

添加为状态的最后一天

添加状态时间（以天为单位）

创建基于状态时间（以天为单位）的堆积柱形图

添加状态时间（以天为单位 ） - 最新 (是状态) 的最后一天

添加状态时间（以天为单位 - 正在进行中）

多个状态的状态时间（以天为单位）趋势

获取多个状态的状态时间（以天为单位- 最新）

添加上一个状态

添加状态已更改

添加状态流

添加状态更改计数

添加状态更改计数 - 上次建议和状态重启时间（以天为单位）

添加状态返工时间（以天为单位）

DAX 函数

相关文章

反馈

其他资源

添加状态时间（以天为单位） - 最新 (是状态) 的最后一天