从数据到信息
从信息到洞察

计算期初和期末余额 - 半累加度量值

计算期末余额

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

OPENINGBALANCE 和 CLOSINGBALANCE

DAX 提供了多个标准函数来获取区间(年、季度或月份)的第一个和最后一个日期,当外部上下文中的时间区间小于目标区间时、你可以使用这类函数获得在目标区间的计值结果,例如:当行标签显示月份级别时,你可能还希望在同一行显示季度末和年末的值,如图 7-31 所示。

图 7-31 每个月的月末、季度末和年末的值总是对应的期末余额

此示例中使用的原始数据包括截止 12 月 31 日的余额。正因如此,我们使用的 DAX 函数提供了完整的结果,而所有基于 LASTDATE 的函数在周期(月、季度或年)的最后一天缺失时将无法返回准确结果,这一点非常重要,建议你使用文末的案例文件自己演练以加深印象

用于计算 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 函数 OPENINGBALANCEMONTHOPENINGBALANCEQUARTEROPENINGBALANCEYEAR 在内部使用 FIRSTDATE,而不是 LASTDATE 在目标区间中计值。它们对应的 CALCULATE 公式分别使用 STARTOFMONTHSTARTOFQUARTERSTARTOFYEAR 作为内部筛选器。

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

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

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

在本例中,我们总是显示每个帐户的余额值,就像是我们在当前日期为每个帐户储存了一张快照,即使它的值自上一个日期以来没有更改。如果此条件不成立,则不能使用 OPENINGBALANCE 和 CLOSINGBALANCE 函数,而必须依赖本文后面介绍的“半累加度量值”中使用的方法。

只显示有余额的日期

如果你使用与前一个示例相同的度量值,在数据透视表的日级别上浏览数据,你将看到如图 7-33 所示的结果。

图 7-33 在日级别浏览数据时显示了没有余额数据的行

定义为显示期末数据的计算字段会产生副作用:所有日期都成为可见日期,即使是那些没有余额数据的日期。如果希望只显示定义了余额数据的日期,就必须修改度量值,检查余额表中是否存在数据,方法如下:

[ClosingBalanceMonth2] :=
IF (
    COUNTROWS ( Balances ) > 0,
    CLOSINGBALANCEMONTH ( SUM ( Balances[Balance] ), 'Date'[Date] )
)
[ClosingBalanceQuarter2] :=
IF (
    COUNTROWS ( Balances ) > 0,
    CLOSINGBALANCEQUARTER ( SUM ( Balances[Balance] ), 'Date'[Date] )
)
[ClosingBalanceYear2] :=
IF (
    COUNTROWS ( Balances ) > 0,
    CLOSINGBALANCEYEAR ( SUM ( Balances[Balance] ), 'Date'[Date] )
)

使用此度量值创建的报告如图 7-34 所示

图 7-34 报告使用的度量值仅显示有余额数据的日期

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

半累加度量值

在本文的第一部分中,你已经了解到一种解决此类问题的标准方法:OPENINGBALANCE 和 CLOSINGBALANCE 函数,但是这种方法有一定局限性,因为基于 LASTDATE 的函数在周期(月、季度或年)的最后一天缺失时将无法返回准确结果。在接下来我们将介绍另一种更为灵活的方案,使用 CALCULATE+LASTNONBLANK 等函数计算半累加度量

每当你使用 SUMCOUNTMINMAX 定义度量值时, 它们都是完全累加度量值, 因为我们可以从任意维度聚合数据。

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

如果要对库存表的数量列进行聚合,那么唯一不适合使用的维度就是日期。对于日期属性,必须只考虑区间最后一个日期的值。换句话说,你必须实现一个逻辑,该逻辑可以产生你在图 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 函数、并筛选包含在第一个可用日期到该区间最后一个日期之间的最后一个非空日期的数据来实现。

对日期范围的考虑需要从当前区间之外开始:例如你可能会计算二月的余额,但那个月可能没有数据,因此你还必须考虑区间开始前的日期。(在图 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。它们各自有另外两个孪生函数用来获取起始日期:FIRSTDATEFIRSTNONBLANK。你将在本系列后面的文章中找到有关这些函数的更多示例。

14
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
4 评论数
12 被回复的评论
5 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
153****1882

图7-29中pen e150在2008年1月26的库存用的是最近日期1月12日的数值,对应的度量值是[On Hand Quantity Last Date by Product],没弄明白如在1月26日是怎么识别到1月12日的?
根据度量值公式,datesbetween的第一参数’Date'[Date]的筛选上下文应该是1月26日,第二参数和第三参数也是1月26日,lastnonblank迭代一个数据,是怎么让它返回了1月12日?谢谢!

Screenshot_20230401_140723_com.baidu.searchbox_edit_725640698626774.jpg
成员
小本本

上下文转换使行上下文无效。虽然我们已经多次重复这个概念,但它值得再次引起您的注意。CALCULATE计算的表达式中没有任何有效的外部行上下文。所有外部行上下文都被转换为等效的筛选上下文。

————————–
老师这个红框部分,的外部计算环境包含sumx对product迭代产生的行上下文吗?

Snipaste_2023-03-27_17-37-43 (小).png
成员
fzn002006

最后一个示例,逻辑是求透视表的每个类目下,在库存表有交易的日期下,每个产品不为空的最后一个日期的库存数量之和?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数