自定义时间智能

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

本文包含四部分：

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

当你无法使用标准时间智能函数时，要如何进行时间智能计算呢？，最常见的做法是将业务逻辑的一部分移动到日期表。标准 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 列而不是从日期中提取年份来编写当前年份的累计至今度量值。该技巧和“使用基础函数重写 DATESYTD”部分中看到的相同。增加的 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 稍作修改，可以很方便的编写出周累计公式 ISO WTD Sales:

[ISO WTD Sales] :=
IF (
    HASONEVALUE ( 'Date'[ISO Week Sequential] ),
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALL ( 'Date' ),
            AND (
                'Date'[ISO Week Sequential] = VALUES ( 'Date'[ISO Week Sequential] ),
                'Date'[Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

基于自定义日历表编写累计公式

在之前 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 的写法，它可以查找上一年中对应的相同日期。但是，如果你有一套基于月的标准日历，那情况将变得更加复杂。

自定义区间比较

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