自定义时间智能

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

正如你到目前为止所看到的，标准的 DAX 时间智能函数只支持公历，当你想要按年、季度、月和日这些标准字段分析数据时，公历很实用。但是，你可能对日期区间的定义有不同的需求，比如不是按月对应，而是基于周来计算。无论出于什么原因，当你不能使用标准的时间智能函数时，你都需要重写时间智能函数的逻辑。

本文包含四部分：

基于周的时间智能
基于自定义日历表编写累计公式
计算不连续周期
区间自定义比较

当你无法使用标准时间智能函数时，要如何进行时间智能计算呢？，最常见的做法是将业务逻辑的一部分移动到日期表。标准 DAX 时间智能函数不会从日期表中读取除日期列以外的任何信息，这是 DAX 的设计初衷，因为这样语言的行为就不依赖于定义更多的元数据来识别用于确定日期的年份、季度和月份的列 (MDX 和多维分析服务就是这种情况），你可以在代码中做更多的假设，这有助于简化代码编写，以便处理与时间相关的自定义计算。

基于周的时间智能

DAX 不提供处理周的时间智能函数，原因是对周的定义和计算方式有许多不同的标准。周经常会跨越年、季度和月的界限。你必须自己编写代码来实现个性化的基于周的日历表的定义。例如，在 ISO 标准的周日期系统中，2011 年 1 月 1 日和 1 月 2 日所在的周是 2010 年的第 52 周，2011 年的第一周从 1 月 3 日开始。

即使有不同的标准，你也可以学习一种在大多数情况下都有效的通用方法，将日期表中的周分配到月/季度/年。后续更改规则只需要更改日期表的内容，而不需要修改度量值里的 DAX 代码。

例如，你可以使用以下计算列扩展日期表对周的划分:

Date[Calendar Week Number] = WEEKNUM ( 'Date'[Date], 1 )
Date[ISO Week Number] = WEEKNUM ( [Date], 21 )
Date[ISO Year Number] =
IF (
[ISO Week Number] < 5
&& [Calendar Week Number] > 50,
[Calendar Year Number] + 1,
IF (
[ISO Week Number] > 50
&& [Calendar Week Number] < 5,
[Calendar Year Number] - 1,
[Calendar Year Number]
)
)
Date[ISO Week] = "W" & [ISO Week Number] & "-" & [ISO Year Number]
Date[ISO Week Sequential] = INT ( ( 'Date'[Date] - 2 ) / 7 )
Date[ISO Year Day Number] =
COUNTROWS (
FILTER (
Date,
AND (
'Date'[ISO Year Number] = EARLIER ( 'Date'[ISO Year Number] ),
'Date'[Date] <= EARLIER ( 'Date'[Date] )
)
)
)

复制代码

你可以在图 7-44 中看到效果。ISO Week 列对用户是可见的，而 ISO Week Sequential Number 对客户端工具是不可见的，ISO 年的日期数是自 ISO 年开始以来的天数。这样的数字可以方便的用作不同时期的比较。

图 7-44 计算列扩展后的日期表支持 ISO 标准周

你可以通过使用 ISO Year Number 列而不是从日期中提取年份来编写当前年份的累计至今度量值。该技巧和“IF 判断用于确保选择了单个 ISO Year 来执行 VALUES 函数，以避免报错。

[ISO YTD Sales] :=
IF (
HASONEVALUE ( 'Date'[ISO Year Number] ),
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
AND (
'Date'[ISO Year Number] = VALUES ( 'Date'[ISO Year Number] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
)
)

复制代码

下图显示 2008 年第一周（包括 2007 年 12 月 31 日）ISO YTD Sales 指标的结果。

图 7-45 在 2008 年的第一周，ISO YTD Sales 包括了 2007 年 12 月 31 日

公式将当前周数与前一年相同的周数进行比较。因为天数可能并不完全相同，因此使用日期表的其他列来比较更为简单。每年的周数分布很有规律，因为每周总是 7 天，而月份有不同的天数，不适用相同的假设。在基于周的日历中，可以通过在上一年份中查找与当前筛选上下文相同选择的对应日期来简化计算。

下面的 ISO PY Sales 度量值筛选前一年中相同的天数。这种方法也适用于外部上下文包含完整周的情况，因为所选天数使用的是 ISO Year Day Number 值，而不是日期值。

[ISO PY Sales] :=
IF (
HASONEVALUE ( 'Date'[ISO Year Number] ),
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
AND (
'Date'[ISO Year Number]
= VALUES ( 'Date'[ISO Year Number] ) - 1,
CONTAINS (
VALUES ( 'Date'[ISO Year Day Number] ),
'Date'[ISO Year Day Number], 'Date'[ISO Year Day Number]
)
)
)
)
)

复制代码

图 7-46 ISO PY Sales 显示了同一周在前一年的值

基于周的日历很容易管理，因为不同年份中周的天数始终是 7 天。而月份的天数可能会有变化，因此如果你想同时使用两个层级结构（月和周），你必须为每个层级结构创建不同的时间智能计算。

周累计（WTD）公式

将 ISO YTD Sales 稍作修改，可以很方便的编写出DATESYTD 一节中，你已经学会了如何用基础函数重写 DATESYTD 以及其他类似的时间智能函数。在本节中，我们将演示如何使用日期表的其他列替换从日期中提取的信息。

例如，观察下面这个 YTD 销售度量值：

[YTD Sales] := CALCULATE ( [Sales Amount],DATESYTD ( 'Date'[Date] ))

复制代码

使用原生 DAX 函数重写后的语法：

[YTD Sales] :=
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date'[Date] ),
AND (
'Date'[Date] <= MAX ( 'Date'[Date] ),
YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
)
)
)

复制代码

如果使用自定义日历表，你需要将对 YEAR 函数的调用替换为对年份列的调用，例如下面的 YTD Sales Custom 度量值，FILTER 迭代整张日期表，以便行上下文可以访问所有列。如果外部上下文包含多个年份，则使用 MAX 获得最后一年。

[YTD Sales Custom] :=
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
AND (
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Calendar Year Number] = MAX ( 'Date'[Calendar Year Number] )
)
)
)

复制代码

使用同样的方式我们可以编写出 QTD 和 MTD 度量值，唯一的区别在于将 Calendar Year Number 列替换为所需的列。

[QTD Sales Custom] :=
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
AND (
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Calendar Year Quarter Number]
= MAX ( 'Date'[Calendar Year Quarter Number] )
)
)
)
[MTD Sales Custom] :=
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
AND (
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Calendar Year Month Number] = MAX ( 'Date'[Calendar Year Month Number] )
)
)
)

复制代码

你可以使用这些公式实现基于标准日历表的计算(在必须使用 DirectQuery 的情况下)和自定义日历表的计算(在非标准时间区间的情况下)。

基于不连续周期的计算

标准的 DAX 时间智能函数不支持计算非连续时间段。正如你在 DATEADD 一文中看到的，DATEADD 函数仅适用于连续日期选择。但是借助自定义 DAX 公式，你可以在这个场景下计算。在基于周的时间智能一节中，你已经看到了一种使用 CONTAINS 的写法，它可以查找上一年中对应的相同日期。但是，如果你有一套基于月的标准日历，那情况将变得更加复杂。

为了便于讨论问题，我们先设定几个初始条件

因为日历是基于月份的，所以我们先实现对单月的计算，然后对上下文中的每月重复相同的计算。
如果选择一个月内的全部日期，平移后的月份也将包括全部日期（即使它们的值不同，例如，从 30 天移动到 31 天，反之亦然）。
如果只选择一个月中的几天，平移后的月份只包含当月对应的相同天数，前提是它们必须存在于平移后的月份中。

对计算不连续周期而言，最重要的就是明确特定情形下使用的计算逻辑

为了简化 DAX 度量值中的计算，我们创建下面的计算列：

Date[Month Sequential Number] = 'Date'[Calendar Year Number] * 12 + 'Date'[Month Number] - 1
Date[Days in Month] =
COUNTROWS (
FILTER (
ALL ( Date ),
'Date'[Month Sequential Number] = EARLIER ( 'Date'[Month Sequential Number] )
)
)
Date[Day of Month] = DAY ( 'Date'[Date] )

复制代码

Month Sequential Number 是每个月和年组合的唯一值，列值每月加 1。这也使得从 1 月移动到前一年的 12 月更容易，你只需在此列中查找 1 月对应的值再减去 1 即可。Days in Month 列包含该月的总天数(例如，1 月 31 日、2 月 28 日或 29 日，依此类推)。Day of Month 列是当前月中的日期序号。你可以在图 7-47 中看到这些列的内容示例。

图 7-47 计算列扩展了日期表, 以支持在非连续区间上使用自定义时间智能计算

让我们看看如何逐步构建完整的公式，以在不使用 DATEADD 的情况下获取上个月的值，DATEADD 具有以下语法：

DATEADD ( 'Date'[Date], -1, MONTH )

复制代码

正如我们在第一个假设中所说的，我们将迭代选定的月份，对筛选上下文中的每个月重复相同的计算：

SUMX (
VALUES ( 'Date'[Month Sequential Number] ),
<calculation for the month>
)

复制代码

每个月发生的计算是不同的，这取决于上下文中是否包括该月的所有日期。月份计算的第一部分执行此检查，将上下文中的天数与月份中的天数进行比较。请记住，此步骤是逐月执行的，这要归功于 SUMX 对 Month Sequential Number 列的迭代

IF (
CALCULATE (
COUNTROWS (
VALUES ( 'Date'[Date] )
)
)
= CALCULATE (
VALUES ( 'Date'[Days in Month] )
),
<月份全部选中时发生的计算>,
<月份部分选中时发生的计算>
)

复制代码

对列的迭代保证了每次迭代在上下文转换后都只有一个当前行，这使得不必添加 HASONEVALUE 检测以保护对 VALUES 的调用。但是，我们仍然需要调用 CALCULATE 或 CALCULATETABLE 来执行上下文转换

如果所选天数等于该月的全部天数，我们需要创建一个筛选器参数，通过从 Month Sequential Number 列中减去一天来选择上个月的所有天数。请注意，下面摘录的代码包含在 SUMX 对日期表 Month Sequential Number 列的迭代中，该列提供了我们要引用的行上下文，使用的是 EARLIER 函数。

CALCULATE (
[Sales Amount],
ALL ( 'Date' ),
FILTER (
ALL ( 'Date'[Month Sequential Number] ),
'Date'[Month Sequential Number]
= EARLIER ( 'Date'[Month Sequential Number] ) - 1
)
)

复制代码

另一种情况，如果筛选器还包含了来自当前迭代月份中的日期筛选，公式会从 Day of Month 列获得日期筛选器，这段代码在下方加粗显示：

CALCULATE (
[Sales Amount],
ALL ( 'Date' ),
CALCULATETABLE ( VALUES ( 'Date'[Day of Month] ) ),
FILTER (
ALL ( 'Date'[Month Sequential Number] ),
'Date'[Month Sequential Number]
= EARLIER ( 'Date'[Month Sequential Number] ) - 1
)
)

复制代码

你可能会惊讶于筛选器参数的语法可以使用下面这种形式：

CALCULATETABLE ( VALUES ( 'Date'[Day of Month] )

复制代码

原因是 CALCULATETABLE 执行上下文转换，转换了 SUMX 在筛选上下文中迭代的 Month Sequential Number 的当前值。这样，只有与迭代月份中的活动日期相对应的 Day of Month 值才会包含在新的筛选上下文中，计值销售金额。

将所有步骤组合起来，你就得到了计算前一个月销售(PM sales Custom)的完整公式：

[PM Sales Custom] :=
SUMX (
VALUES ( 'Date'[Month Sequential Number] ),
IF (
CALCULATE (
COUNTROWS (
VALUES ( 'Date'[Date] )
)
)
= CALCULATE (
VALUES ( 'Date'[Days in Month] )
),
CALCULATE (
[Sales],
ALL ( 'Date' ),
FILTER (
ALL ( 'Date'[Month Sequential Number] ),
'Date'[Month Sequential Number]
= EARLIER ( 'Date'[Month Sequential Number] ) - 1
)
),
CALCULATE (
[Sales],
ALL ( 'Date' ),
CALCULATETABLE (
VALUES ( 'Date'[Day of Month] )
),
FILTER (
ALL ( 'Date'[Month Sequential Number] ),
'Date'[Month Sequential Number]
= EARLIER ( 'Date'[Month Sequential Number] ) - 1
)
)
)
)

复制代码

你可以在图 7-48 中看到，该度量值现在也适用于不连续的选择，因为在 2008 年 3 月到 2008 年 8 月之间的月份没有在行上显示。但是，PM Sales Custom 列在 2008 年 1 月返回对应的 2007 年 12 月的值，在 2008 年 9 月返对应的 2008 年 8 月的值

图 7-48 PM Sales Custom 显示了前一个月的销售额

使用相同的公式，你可以通过简单地从 Month Sequential Number 中减去 3 或 12 而不是 1 来实现与前一个季度或前一年的比较。

自定义区间比较

在上一节中，你已经了解了如何在含月份的日期表中实现对前一个时间段的计算。你可以把同样的方法应用到一年中的任何时间段。但是，自定义日历可能需要应用特殊的规则才能进行比较，将一年中的特定时段（例如劳动节、端午节、复活节、新年以及任何其他可能的假日）排除在外。在本节中，你将学习一种在数据模型中处理这些规则的方法，而不必在 DAX 表达式中加入判断逻辑。

最初的假设是，应该可以为每一天定义前一年对应的日期。例如，我们假设默认情况下，它们都是同一工作日，用当前的天数减去 364 天(正好是 52 周)。不过，春节等例外情况可能会修改结果（例如，2019 年的 2 月 5 日和 2020 年的 1 月 25 日)。在时间智能函数内部处理这种情况会增加计算复杂性，并消耗更长的执行时间。因此，我们考虑在日期表中定义一个列，用前一年对应的日期填充它，如图 7-49 所示。

图 7-49 列 Date Previous Year 定义了在与前一年对比时使用的日期

使用自定义公式计算上年度销售额的写法如下：

[PY Sales Custom] :=
CALCULATE (
[Sales Amount],
FILTER (
ALL ( 'Date' ),
CONTAINS (
VALUES ( 'Date'[Date Previous Year] ),
'Date'[Date Previous Year], 'Date'[Date]
)
)
)

复制代码

FILTER 函数返回与前一年日期列中的活动日期对应的日期。例如，用户选择 2005 年 1 月 8 日；Date Previous Year 列的当前筛选器是 2004 年 1 月 10 日。FILTER 迭代所有日期并返回日期列等于 2004 年 1 月 10 日的行。使用 CONTAINS 可以处理所选的任意数量的日期。

FILTER 返回的日期必须存在于日期表的日期列中。此外，结果将永远不包括重复的日期。如果在 Date Previous Year 列中具有重复值，则重复值将仅由筛选器返回一次。由于以上原因，FILTER 返回的行数可能低于筛选上下文中最初选择的日期数。

在图 7-50 中可以看到 PY Sales Custom 的结果。为了显示计算去年所考虑的日期范围，还增加了下列度量值：