介绍时间智能函数 - DAX圣经

引言

几乎所有的业务逻辑都会涉及到关于时间的计算，比如年累计销售额，月环比销售额，YOY（Year over Year）等等，DAX 提供专注解决此类问题的函数，当模型满足特定要求的时候，时间智能函数可以大大简化运算。

在本章中，通过学习常见的时间智能函数，你将掌握时间计算的奥秘，如年初至今累计、年同比和多个年份的对比等等，也包括非累加度量值和半累加度量值，你将学习如何使用特定的时间智能函数计算这两种度量值。还将了解到如何在非标准日期表环境下使用自定义 DAX 公式以及实现基于周的计算。

时间智能函数可以简化运算，但这其中也蕴含了一些复杂性，这种复杂性隐藏在那些构成时间智能函数的基本函数中。是的，时间智能函数本身并不是底层函数，它们是由像 CALCULATE、聚合函数这样的基本函数为实现特定的计算逻辑组合而成，为了避免每次输入冗长的公式，我们将其赋予一个通俗易懂，且容易使用的短名，这也是时间智能函数的由来。这类语法有一个更通用的名称：语法糖

本章将介绍常用时间智能函数背后的通用公式，它可以帮助你彻底理解时间智能函数，并在某些特定场景下规避副作用、用基础函数写出适用于特定情境的“时间智能”公式。

介绍日期表

数据模型通常会包含不同颗粒度的时间信息，当你需要按年、月或其他时间粒度聚合数据时，使用日期表中的列是更好的选择，而不是从事实表中新建计算列提取日期部分，也不是直接使用事实表的日期列，这些都是错误且危险的做法，原因是：

模型中的所有日期属性都包含在一个单独的表中, 可以更轻松地通过一张表控制整个模型的日期计算。
DAX 提供专门的函数来执行时间智能计算。而且，大多数时间智能函数都需要连续且完整的日期才能正常工作，否则会报错。单独的日期表可以满足这个条件。
连续日期的要求是：日期表首末日期之间的所有日期都必须包含在日期表中。
完整日期的要求是：必须包含完整的年，比如从 2019 年 1 月 1 日到 2019 年 12 月 31 日；或者完整的财年，比如 2018 年 7 月 1 日到 2019 年 6 月 30 日（7 月 1 日是财年的第一天）。当日期表日期不完整的时候，可能遇到意想不到的错误，参考 SAMPERIODLASTYEAR。

与模型建立关系

在星型模型中定义单独的日期表是一种常见的做法。你应该对任何模型都使用这种技术，即便在开始还不是星形模型的时候。当需要分析日期列时，你需要创建一个日期列与日期表的关系。如果在一个表中有多个需要分析的日期列，那么除了单个活动关系外，你还可以创建连接到日期表的多个非活动关系，如下图中的销售表所示

销售表和日期表有一条活动关系，两条非活动关系

你还可以选择为每个日期列创建不同的日期表。在本章的后面，我们将讨论这两种选择。

无论如何，当数据中有一个或多个日期列时，都应该在模型中至少创建一个日期表。

时间智能函数在钻取时的特殊行为

我们知道 Excel 透视表支持双击值区域的单元格查看明细数据，这个功能可以让你快速查看当前筛选条件下数据源的所有记录。但是，当使用时间智能函数时，公式都更改了日期表的筛选上下文，从而得到不同于初始筛选上下文的计值结果。在使用支持报表钻取操作的客户端（例如 Excel 中的数据透视表）时，你可以观察到意料之外的结果：明细数据只受到来自外部的筛选上下文的影响。原因是钻取操作由 MDX 执行，它不考虑度量值内部定义的筛选上下文，而只接受由透视表的行、列、过滤器和切片器定义的筛选上下文环境。

例如，对 2007 年 3 月的钻取始终返回这个时间段内的明细数据，与度量值应用的时间智能函数无关。比如

使用 TOTALYTD 计算累计值，钻取后你预期返回 2007 年 1 月至 3 月的所有日期；
使用 SAMEPERIODLASTYEAR 计算去年同期值，钻取后你预期返回 2006 年 3 月的所有日期，
使用 LASTDATE，钻取后你预期只得到 2007 年 3 月 31 日所在行。

不幸的是，对以上度量值的钻取都只返回 2007 年 3 月的数据。而且，这种行为是故意设计的。

不仅是时间智能函数，所有通过 DAX 公式创建的筛选上下文都不能反应在钻取的结果中

时间智能函数一览

函数	说明
CLOSINGBALANCEMONTH	计算当前上下文中该月最后一个日期的表达式。
CLOSINGBALANCEQUARTER	计算当前上下文中该季度最后日期的表达式。
CLOSINGBALANCEYEAR	计算当前上下文中该年最后一个日期的表达式。
DATEADD	返回一个表，该表包含日期的列，按指定的时间间隔（从当前上下文中的日期开始向前或向后移动）。
DATESBETWEEN	返回一个表，该表包含以 start_date 开头并持续到 end_date 的日期列。
DATESINPERIOD	返回一个表，其中包含一个日期列，该列的日期从 start_date 开始，并继续指定的 number_of_intervals。
DATESMTD	返回一个表，该表包含当前上下文中的本月截止日期的列。
DATESQTD	返回一个表，该表包含当前上下文中的季度截止到现在的日期列。
DATESYTD	返回一个表，该表包含当前上下文中当前年份的日期列。
ENDOFMONTH	返回当前上下文中指定日期列的最后一个月的日期。
ENDOFQUARTER	返回当前上下文中指定日期列的季度最后一个日期。
ENDOFYEAR	返回当前上下文中指定日期列的年份的最后日期。
FIRSTDATE	返回当前上下文中指定日期列的第一个日期。
FIRSTNONBLANK	返回按当前上下文筛选的列列中的第一个值，其中表达式不为空
LASTDATE	返回当前上下文中指定日期列的最后日期。
LASTNONBLANK	返回按当前上下文筛选的列列中的最后一个值，其中表达式不为空。
NEXTDAY	返回一个表，其中包含从下一天起的所有日期的列，它基于当前上下文中日期列中指定的第一个日期。
NEXTMONTH	返回一个表，其中包含下个月中的所有日期的列，它基于当前上下文中日期列中的第一个日期。
NEXTQUARTER	返回一个表，其中包含下一季度中的所有日期的列，它基于当前上下文中 “日期” 列中指定的第一个日期。
NEXTYEAR	返回一个表，其中包含下一年的所有日期的列，它基于当前上下文中日期列中的第一个日期。
OPENINGBALANCEMONTH	计算当前上下文中该月第一个日期的表达式。
OPENINGBALANCEQUARTER	计算当前上下文中该季度第一个日期的表达式。
OPENINGBALANCEYEAR	计算当前上下文中该年度第一个日期的表达式。
PARALLELPERIOD	返回一个表，其中包含一个日期列，该日期表示与当前上下文中指定日期列中的日期并行的时间段，其中的日期在时间中向前或向后移动。
PREVIOUSDAY	返回一个表，其中包含表示当前上下文中日期列中第一个日期之前日期的所有日期的列。
PREVIOUSMONTH	返回一个表，该表包含上个月中的所有日期的列，该列基于当前上下文中日期列中的第一个日期。
PREVIOUSQUARTER	返回一个表，该表包含上一季度的所有日期的列，该列基于当前上下文中日期列中的第一个日期。
PREVIOUSYEAR	返回一个表，该表包含在当前上下文中的日期列中的最后一个日期之后的所有日期的列。
SAMEPERIODLASTYEAR	返回一个表，其中包含从当前上下文中指定日期列中的日期起返回一年的日期的列。
STARTOFMONTH	返回当前上下文中指定日期列的第一个月的日期。
STARTOFQUARTER	返回当前上下文中指定日期列的季度第一天的日期。
STARTOFYEAR	返回当前上下文中指定日期列的年份的第一个日期。
TOTALMTD	在当前上下文中计算本月截止日期的表达式的值。
TOTALQTD	计算当前上下文中的季度截止日期的表达式的值。
TOTALYTD	计算当前上下文中的表达式的年初至今值。