12.7 计算期初和期末余额


# 计算期末余额 通过之前的文章，你已经了解如何使用 DAX 创建与时间相关的聚合。但是，某些情况下我们不能从时间维度聚合数据。例如，你不能将每日的账户余额或者产品库存加总得到每月的数据，而是要使用月末最后一天的数据作为月度数据，但是我们可以从其他非时间维度聚合度量值，我们称这类度量值为半累加度量值，在这一节中，你会看到如何在 DAX 中定义它们。

OPENINGBALANCE 和 CLOSINGBALANCE

DAX 提供了多个标准函数来获取区间（年、季度或月份）的第一个和最后一个日期，当外部上下文中的时间区间小于目标区间时、你可以使用这类函数获得在目标区间的计值结果，例如：当行标签显示月份级别时，你可能还希望在同一行显示季度末和年末的值，如图 7-31 所示。 12.7 计算期初和期末余额 - 图1图 7-31 每个月的月末、季度末和年末的值总是对应的期末余额 而所有基于 LASTDATE 的函数在周期（月、季度或年）的最后一天缺失时将无法返回准确结果，用于计算 ClosingBalanceMonth、ClosingBalanceQuarter 和 ClosingBalanceYear 的公式如下：

  1. [ClosingBalanceMonth] := CLOSINGBALANCEMONTH ( SUM ( Balances[Balance] ), ‘Date’[Date])
  2. [ClosingBalanceQuarter] := CLOSINGBALANCEQUARTER ( SUM ( Balances[Balance] ), ‘Date’[Date] )

  3. [ClosingBalanceYear] := CLOSINGBALANCEYEAR ( SUM ( Balances[Balance] ), ‘Date’[Date] ) 这些公式在内部使用了 LASTDATE 函数，它们对一组日期进行操作，这些日期可以扩展透视表中的当前筛选上下文。例如，CLOSINGBALANCEYEAR 函数考虑’Date’[Date]列的最后日期，将包含此日期列的最后一年应用到筛选上下文。因此，对于 2010 年 2 月(以及 2010 年的任何一个月或季度)，这个日期是 2010 年 12 月 31 日。
    CLOSINGBALANCEYEAR 函数的行为类似于使用 ENDOFYEAR 函数作为筛选器的 CALCULATE 表达式。和之前提到的一样，CALCULATE 的写法更加通用和灵活，但是像 CLOSINGBALANCEYEAR 这样的特定 DAX 函数可以更好地表达度量值设计者的意图。以下使用 CALCULATE 语法的表达式与之前的公式等价：

  4. [ClosingBalanceEOM] :=

  5. CALCULATE ( SUM ( Balances[Balance] ), ENDOFMONTH ( ‘Date’[Date] ) )
  6. [ClosingBalanceEOQ] :=
  7. CALCULATE ( SUM ( Balances[Balance] ), ENDOFQUARTER ( ‘Date’[Date] ) )
  8. [ClosingBalanceEOY] :=

  9. CALCULATE ( SUM ( Balances[Balance] ), ENDOFYEAR ( ‘Date’[Date] ) ) 你必须考虑模型中可用数据的日期。如果将数据透视表向下钻取到的日级别的数据, 就会看到这一点。在执行此操作之前, 请考虑我们在此示例中使用的原始数据集, 如图 7-32 所示。 12.7 计算期初和期末余额 - 图2图 7-32 每个月的余额多次出现, 增加了计算的难度 正如你所看到的, 每个月有多笔余额记录。例如, 1 月份的第 8 天、第 15 天、第 22 天和第 31 天。 半累加度量### 只显示有余额的日期 如果你使用与前一个示例相同的度量值，在数据透视表的日级别上浏览数据，你将看到如图 7-33 所示的结果。 12.7 计算期初和期末余额 - 图3图 7-33 在日级别浏览数据时显示了没有余额数据的行 定义为显示期末数据的计算字段会产生副作用：所有日期都成为可见日期，即使是那些没有余额数据的日期。如果希望只显示定义了余额数据的日期，就必须修改度量值，检查余额表中是否存在数据，方法如下：

  10. [ClosingBalanceMonth2] :=

  11. IF (
  12. COUNTROWS ( Balances ) > 0,
  13. CLOSINGBALANCEMONTH ( SUM ( Balances[Balance] ), ‘Date’[Date] )
  14. )
  15. [ClosingBalanceQuarter2] :=
  16. IF (
  17. COUNTROWS ( Balances ) > 0,
  18. CLOSINGBALANCEQUARTER ( SUM ( Balances[Balance] ), ‘Date’[Date] )
  19. )
  20. [ClosingBalanceYear2] :=
  21. IF (
  22. COUNTROWS ( Balances ) > 0,
  23. CLOSINGBALANCEYEAR ( SUM ( Balances[Balance] ), ‘Date’[Date] )

  24. ) 使用此度量值创建的报告如图 7-34 所示 12.7 计算期初和期末余额 - 图4图 7-34 报告使用的度量值仅显示有余额数据的日期 默认情况下，许多客户端工具(包括本例中使用的 Microsoft Excel 数据透视表)不显示空行和空列。因此，没有显示不包含余额的日期。图 7-34 中使用的所有度量值在那些日期都返回空白，客户端自动将其隐藏了。

    半累加度量值

    在本文的第一部分中，你已经了解到一种解决此类问题的标准方法：OPENINGBALANCE 和 CLOSINGBALANCE 函数，但是这种方法有一定局限性，因为基于 LASTDATE 的函数在周期（月、季度或年）的最后一天缺失时将无法返回准确结果。在接下来我们将介绍另一种更为灵活的方案，使用 CALCULATE+LASTNONBLANK 等函数计算半累加度量。
    每当你使用 SUM、COUNT、MIN 或 MAX 定义度量值时, 它们都是完全累加度量值, 因为我们可以从任意维度聚合数据。
    有时，你需要度量值以另一种方式计值。例如在计算产品库存时，如果你需要了解某些产品的库存量，可以通过计算某一天属于该类别的产品数量作为库存。但是，同一种产品的数量不能跨天相加，否则结果将不能表示真实库存。
    如果要对库存表的数量列进行聚合，那么唯一不适合使用的维度就是日期。对于日期属性，必须只考虑区间最后一个日期的值。换句话说，你必须实现一个逻辑，该逻辑可以产生你在图 7-23 中看到的结果，对整个区间的计值结果与最后一个周期相同（例如，2008 年第一季度的数值与 2008 年 3 月相同，2008 年第二季度的数值与 2008 年 6 月的数值相同，以此类推） 12.7 计算期初和期末余额 - 图5图 7-23 半累加度量值 On Hand Quantity Simple 并非在季度和年份上执行聚合 图 7-23 中使用的 On Hand Quantity Simple 公式显示了一个季度的总数，该数据与季度最后一个月的值相同。如果库存表每天都有统计数据，你可以使用以下公式（注意：这不是最佳解决方案；最佳方法将在本节稍后介绍）

  25. [On Hand Quantity Simple] :=

  26. CALCULATE (
  27. SUM ( Inventory[Quantity] ),
  28. LASTDATE ( Inventory[Date] )

  29. ) On Hand Quantity Simple 使用 LASTDATE 只保留当前活动筛选上下文中的最后一个日期。因此，CALCULATE 在调用筛选器时只考虑每个单元格有可用数据的最后一个日期。

    使用日期表的日期列

    值得注意的是，在这种情况下，LASTDATE 使用的 Inventory[Date]是库存表的一个日期列，它与日期表建立了关系。通过使用库存表的日期列，公式只考虑库存中经过筛选的行，这可能有一个有趣的副作用。对于每个单元格，只考虑所选产品和周期约束下的最后可用日期。例如，查看图 7-24 中生成的结果，其中最后一列显示了产品的总数。 12.7 计算期初和期末余额 - 图6图 7-24 On Hand Quantity Simple 的总计没有像预期那样汇总产品数量 如果你考虑 2008 年 1 月的数据，“Contoso in – line Coupler E180 White”产品在 1 月 26 日的数量是 42，“Contoso Touch Stylus Pen E150 Silver”在 1 月 12 日的数量是 200，而 2008 年 1 月的总数是 42，但是正确的值应该是多少呢？这取决于在库存表的计算方式。当你每天记录所有产品的库存时，你就不会看到这种行为。如果某个产品的日期存在缺失，那么这意味着该产品当天没有库存。在这个场景中，总计的值是正确的，但是“Contoso Touch Stylus Pen E150 Silver”在当月的总计是错误的，因为它应该为空(1 月 26 日没有库存)。为了解决这个问题，最好使用日期表的 Date[Date]列而不是 Inventory[Date]列作为传递给 LASTDATE 函数的参数。

  30. [On Hand Quantity Last Date] :=

  31. CALCULATE (
  32. SUM ( Inventory[Quantity] ),
  33. LASTDATE ( ‘Date’[Date] )

  34. ) 在这种情况下，区间的最后日期是日期表的最后日期，而不是有事实表的最后日期。因此，对于一月，它将始终是 1 月 31 日，即使库存表在当日没有记录。然而，这可能会带来意想不到的后果。如果事实表在月的最后一天没有记录，并且日期表包含该月所有日期（毫无疑问），使用 LASTDATE 定义的 On Hand Quantity Last Date 公式将不会返回任何数据(一个空白值)，如图 7-25 所示。 12.7 计算期初和期末余额 - 图7图 7-25 On Hand Quantity Last Date 在一月份的总计为空，因为 1 月 31 日没有数据 如果 1 月 31 日存在库存数据的记录，而且图 7-25 中的两款产品在当日也没有库存，那么基于 LASTDATE 的公式结果是正确的。然而，事实并非如此。Contoso 数据库中的库存数据以周为粒度。你可以通过按日期汇总所有产品来显示 On Hand Quantity Last Date 的值，如图 7-26 所示。 12.7 计算期初和期末余额 - 图8图 7-26 On Hand Quantity Last Date 的以周为粒度计算，可能不包括每月的最后一天

    使用 LASTNONBLANK

    如果希望在月份级别上查看所有发生交易的天数中最后一天的值，而忽略未发生交易的日期，则必须使用另一种方法。解决方案是使用 LASTNONBLANK 函数，该函数返回特定表达式不为空的最后一个日期。下面是一个使用 LASTNONBLANK 函数计算 On Hand Quantity 的公式：

  35. [On Hand Quantity] :=

  36. CALCULATE (
  37. SUM ( Inventory[Quantity] ),
  38. LASTNONBLANK (
  39. ‘Date’[Date],
  40. CALCULATE (
  41. COUNTROWS ( Inventory ),
  42. ALL ( Product )
  43. )
  44. )

  45. ) 通过使用 On Hand Quantity 公式，你可以看到，对于我们之前考虑过的两个产品，1 月份的值现在对应于 1 月 26 日，如图 7-27 所示。 12.7 计算期初和期末余额 - 图9图 7-27 On Hand Quantity 公式正确显示了所选产品在 2008 年 1 月的值 在图 7-28 中你可以看到，对于每个产品类别，月份对应的值来自于库存表行记录的最后一天。 12.7 计算期初和期末余额 - 图10图 7-28 On Hand Quantity 显示日期区间内有数据记录的最后一天的结果 如果库存表只在产品数量发生变化时才记录一行，那么你想要的可能是图 7-29 中展示的结果。 12.7 计算期初和期末余额 - 图11图 7-29 On Hand Quantity Last Date by Product 返回在库存表中每个产品的最后数量 在这种情况下, 库存表删除了现有产品数量与以前快照相同的重复行。而数量为零的记录需要在库存表中新建专门行。这种存储数据的方法是为每笔交易创建新库存值的系统的典型方法，这类系统只更新具有某些交易记录的产品，而不是所有其他交易记录。你可以通过使用 On Hand Quantity Last Date by Product 来实现此计算：

    考虑所有产品的库存余额公式

    对于每个产品，基本思想是你必须获得所选区间包含的最后一个非空日期。单个帐户的计算可以通过使用 CALCULATE 函数、并筛选包含在第一个可用日期到该区间最后一个日期之间的最后一个非空日期的数据来实现。 SUMX

  46. [On Hand Quantity Last Date by Product] :=

  47. IF (
  48. CALCULATE (
  49. COUNTROWS ( Inventory ),
  50. ALL ( ‘Product’ )
  51. ) > 0,
  52. SUMX (
  53. ‘Product’,
  54. CALCULATE (
  55. SUM ( Inventory[Quantity] ),
  56. LASTNONBLANK (
  57. DATESBETWEEN (
  58. ‘Date’[Date],
  59. BLANK (),
  60. LASTDATE ( ‘Date’[Date] )
  61. ),
  62. CALCULATE (
  63. COUNTROWS ( Inventory )
  64. )
  65. )
  66. )
  67. )
  68. ) 在图 7-30 中，你可以看到，对于每个产品类别，其值与图 7-28 中的值不同，因为在每个单元格中，度量值对每个产品的最后可用库存行值进行求和，即使每个产品的日期不同。 12.7 计算期初和期末余额 - 图12图 7-30 On Hand Quantity Last Date by Product 为类别下的每个产品聚合了余额

    总结

    你需要了解数据是如何存储在模型中的，以便于为 On Hand Quantity 的计算选择正确的公式。
    到目前为止，我们接触了两个看起来相似但行为非常不同的时间智能函数：LASTDATE 和 LASTNONBLANK。它们各自有另外两个孪生函数用来获取起始日期：FIRSTDATE 和 FIRSTNONBLANK。你将在本章后面关于函数的文章中找到有关所有这些函数的更多示例和详细信息。