计算期初和期末余额

显示全部楼层 · 2021-2-21 18:55:01

计算期末余额

通过之前的文章，你已经了解如何使用 DAX 创建与时间相关的聚合。但是，某些情况下我们不能从时间维度聚合数据。例如，你不能将每日的账户余额或者产品库存加总得到每月的数据，而是要使用月末最后一天的数据作为月度数据，但是我们可以从其他非时间维度聚合度量值，我们称这类度量值为LASTDATE 的函数在周期（月、季度或年）的最后一天缺失时将无法返回准确结果，这一点非常重要，建议你使用文末的案例文件自己演练以加深印象[/quote]

用于计算 ClosingBalanceMonth、ClosingBalanceQuarter 和 ClosingBalanceYear 的公式如下：

[ClosingBalanceMonth] := CLOSINGBALANCEMONTH ( SUM ( Balances[Balance] ), 'Date'[Date])
[ClosingBalanceQuarter] := CLOSINGBALANCEQUARTER ( SUM ( Balances[Balance] ), 'Date'[Date] )
[ClosingBalanceYear] := CLOSINGBALANCEYEAR ( SUM ( Balances[Balance] ), 'Date'[Date] )

复制代码

这些公式在内部使用了 LASTDATE 函数，它们对一组日期进行操作，这些日期可以扩展透视表中的当前筛选上下文。例如，CLOSINGBALANCEYEAR 函数考虑’Date'[Date]列的最后日期，将包含此日期列的最后一年应用到筛选上下文。因此，对于 2010 年 2 月(以及 2010 年的任何一个月或季度)，这个日期是 2010 年 12 月 31 日。

CLOSINGBALANCEYEAR 函数的行为类似于使用 ENDOFYEAR 函数作为筛选器的 CALCULATE 表达式。和之前提到的一样，CALCULATE 的写法更加通用和灵活，但是像 CLOSINGBALANCEYEAR 这样的特定 DAX 函数可以更好地表达度量值设计者的意图。以下使用 CALCULATE 语法的表达式与之前的公式等价：

[ClosingBalanceEOM] :=
CALCULATE ( SUM ( Balances[Balance] ), ENDOFMONTH ( 'Date'[Date] ) )
[ClosingBalanceEOQ] :=
CALCULATE ( SUM ( Balances[Balance] ), ENDOFQUARTER ( 'Date'[Date] ) )
[ClosingBalanceEOY] :=
CALCULATE ( SUM ( Balances[Balance] ), ENDOFYEAR ( 'Date'[Date] ) )

复制代码

DAX 函数 OPENINGBALANCEMONTH、OPENINGBALANCEQUARTER 和 OPENINGBALANCEYEAR 在内部使用 FIRSTDATE，而不是 LASTDATE 在目标区间中计值。它们对应的 CALCULATE 公式分别使用 STARTOFMONTH、STARTOFQUARTER 和 STARTOFYEAR 作为内部筛选器。

你必须考虑模型中可用数据的日期。如果将数据透视表向下钻取到的日级别的数据, 就会看到这一点。在执行此操作之前, 请考虑我们在此示例中使用的原始数据集, 如图 7-32 所示。

图 7-32 每个月的余额多次出现, 增加了计算的难度

正如你所看到的, 每个月有多笔余额记录。例如, 1 月份的第 8 天、第 15 天、第 22 天和第 31 天。

在本例中，我们总是显示每个帐户的余额值，就像是我们在当前日期为每个帐户储存了一张快照，即使它的值自上一个日期以来没有更改。如果此条件不成立，则不能使用 OPENINGBALANCE 和 CLOSINGBALANCE 函数，而必须依赖本文后面介绍的“LASTNONBLANK 等函数计算SUM、MIN 或 MAX 定义度量值时, 它们都是完全累加度量值, 因为我们可以从任意维度聚合数据。

有时，你需要度量值以另一种方式计值。例如在计算产品库存时，如果你需要了解某些产品的库存量，可以通过计算某一天属于该类别的产品数量作为库存。但是，同一种产品的数量不能跨天相加，否则结果将不能表示真实库存。

如果要对库存表的数量列进行聚合，那么唯一不适合使用的维度就是日期。对于日期属性，必须只考虑区间最后一个日期的值。换句话说，你必须实现一个逻辑，该逻辑可以产生你在图 7-23 中看到的结果，对整个区间的计值结果与最后一个周期相同（例如，2008 年第一季度的数值与 2008 年 3 月相同，2008 年第二季度的数值与 2008 年 6 月的数值相同，以此类推）

图 7-23 半累加度量值 On Hand Quantity Simple 并非在季度和年份上执行聚合

图 7-23 中使用的 On Hand Quantity Simple 公式显示了一个季度的总数，该数据与季度最后一个月的值相同。如果库存表每天都有统计数据，你可以使用以下公式（注意：这不是最佳解决方案；最佳方法将在本节稍后介绍）

[On Hand Quantity Simple] :=

CALCULATE (

SUM ( Inventory[Quantity] ),

LASTDATE ( Inventory[Date] )

)

复制代码

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

使用日期表的日期列

值得注意的是，在这种情况下，LASTDATE 使用的 Inventory[Date]是库存表的一个日期列，它与日期表建立了关系。通过使用库存表的日期列，公式只考虑库存中经过筛选的行，这可能有一个有趣的副作用。对于每个单元格，只考虑所选产品和周期约束下的最后可用日期。例如，查看图 7-24 中生成的结果，其中最后一列显示了产品的总数。

图 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 函数的参数。

[On Hand Quantity Last Date] :=

CALCULATE (

SUM ( Inventory[Quantity] ),

LASTDATE ( 'Date'[Date] )

)

复制代码

在这种情况下，区间的最后日期是日期表的最后日期，而不是有事实表的最后日期。因此，对于一月，它将始终是 1 月 31 日，即使库存表在当日没有记录。然而，这可能会带来意想不到的后果。如果事实表在月的最后一天没有记录，并且日期表包含该月所有日期（毫无疑问），使用 LASTDATE 定义的 On Hand Quantity Last Date 公式将不会返回任何数据(一个空白值)，如图 7-25 所示。

图 7-25 On Hand Quantity Last Date 在一月份的总计为空，因为 1 月 31 日没有数据

如果 1 月 31 日存在库存数据的记录，而且图 7-25 中的两款产品在当日也没有库存，那么基于 LASTDATE 的公式结果是正确的。然而，事实并非如此。Contoso 数据库中的库存数据以周为粒度。你可以通过按日期汇总所有产品来显示 On Hand Quantity Last Date 的值，如图 7-26 所示。

图 7-26 On Hand Quantity Last Date 的以周为粒度计算，可能不包括每月的最后一天

使用 LASTNONBLANK

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

[On Hand Quantity] :=

CALCULATE (

SUM ( Inventory[Quantity] ),

LASTNONBLANK (

      'Date'[Date],

      CALCULATE (

         COUNTROWS ( Inventory ),

         ALL ( Product )

      )

)

)

复制代码

通过使用 On Hand Quantity 公式，你可以看到，对于我们之前考虑过的两个产品，1 月份的值现在对应于 1 月 26 日，如图 7-27 所示。

图 7-27 On Hand Quantity 公式正确显示了所选产品在 2008 年 1 月的值

在图 7-28 中你可以看到，对于每个产品类别，月份对应的值来自于库存表行记录的最后一天。

图 7-28 On Hand Quantity 显示日期区间内有数据记录的最后一天的结果

如果库存表只在产品数量发生变化时才记录一行，那么你想要的可能是图 7-29 中展示的结果。

图 7-29 On Hand Quantity Last Date by Product 返回在库存表中每个产品的最后数量

在这种情况下, 库存表删除了现有产品数量与以前快照相同的重复行。而数量为零的记录需要在库存表中新建专门行。这种存储数据的方法是为每笔交易创建新库存值的系统的典型方法，这类系统只更新具有某些交易记录的产品，而不是所有其他交易记录。你可以通过使用 On Hand Quantity Last Date by Product 来实现此计算：

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

对于每个产品，基本思想是你必须获得所选区间包含的最后一个非空日期。单个帐户的计算可以通过使用 CALCULATE 函数、并筛选包含在第一个可用日期到该区间最后一个日期之间的最后一个非空日期的数据来实现。[quote]对日期范围的考虑需要从当前区间之外开始：例如你可能会计算二月的余额，但那个月可能没有数据，因此你还必须考虑区间开始前的日期。(在图 7-29 中，“Contoso Touch Stylus Pen E150 Silver”的值在 2008 年 1 月 5 日是 120，这是因为在 2007 年有一笔交易，即使它并不在报告使用的日期范围内)

使用 SUMX 函数迭代所有可用产品，为了计算任意产品至少具有一笔交易的日期，必须检查库存表是否至少有一行存在于当前选择的日期中，这就是这种度量值的定义。

[On Hand Quantity Last Date by Product] :=
IF (
CALCULATE (
COUNTROWS ( Inventory ),
ALL ( 'Product' )
) > 0,
SUMX (
'Product',
CALCULATE (
SUM ( Inventory[Quantity] ),
LASTNONBLANK (
DATESBETWEEN (
'Date'[Date],
BLANK (),
LASTDATE ( 'Date'[Date] )
),
CALCULATE (
COUNTROWS ( Inventory )
)
)
)
)
)

复制代码

在图 7-30 中，你可以看到，对于每个产品类别，其值与图 7-28 中的值不同，因为在每个单元格中，度量值对每个产品的最后可用库存行值进行求和，即使每个产品的日期不同。

图 7-30 On Hand Quantity Last Date by Product 为类别下的每个产品聚合了余额

总结

你需要了解数据是如何存储在模型中的，以便于为 On Hand Quantity 的计算选择正确的公式。

到目前为止，我们接触了两个看起来相似但行为非常不同的时间智能函数：LASTDATE 和 LASTNONBLANK。它们各自有另外两个孪生函数用来获取起始日期：FIRSTDATE 和 FIRSTNONBLANK。你将在本章后面关于函数的文章中找到有关所有这些函数的更多示例和详细信息。

70902119363810.rar (2.62 MB, 下载次数: 1)