Excel DAX - 时间智能

DAX 有一个重要而强大的功能，称为时间智能。时间智能使您能够编写引用数据透视表中使用的时间段的 DAX 公式。

DAX 具有 35 个时间智能函数，专门用于聚合和比较一段时间内的数据。但是，这些 DAX 函数对数据有一些限制，您需要了解这些限制并谨慎使用以避免错误。

为什么时间智能让 DAX 变得强大？

时间智能函数处理不断变化的数据，具体取决于您在数据透视表和 Power View 可视化中选择的上下文。如您所知，大多数数据分析涉及对不同时间段内的数据进行汇总、比较不同时间段内的数据值、了解趋势并根据未来预测做出决策。

例如，您可能希望按产品汇总过去一个月的销售额，并将总计与会计年度其他月份的总计进行比较。这意味着，您必须使用日期来对特定时间段内的销售交易进行分组和汇总。

在这里您可以观察到 DAX 的强大功能。您可以使用 DAX 时间智能函数来定义计算字段，帮助您分析一段时间内的数据，而无需更改数据透视表中的日期选择。这使您的工作更加轻松。此外，您可以构建任何其他方式都无法实现的数据透视表。

DAX 时间智能功能的要求

DAX时间智能功能有一定的要求。如果不满足这些要求，您可能会收到错误或它们可能无法正常工作。因此，您也可以将这些要求称为规则或约束。以下是某些 DAX 时间智能功能要求/规则/约束 -

  • 您的数据模型中需要有一个日期表。

  • 日期表必须包含被 DAX 视为日期列的列。您可以按照您想要的方式命名该列，但它应符合以下条件： o 日期列应包含一组连续的日期，涵盖您正在分析数据的时间段内的每一天。

    • 每个日期在日期列中必须存在且仅存在一次。

    • 您不能跳过任何日期（例如，您不能跳过周末日期）。

  • DAX 时间智能函数仅适用于标准日历，并假设年初为 1 月 1 日，年底为 12 月 31 日，一年中的月份和每月的天数均为日历年。

但是，您可以为不同的财务年度自定义标准日历。在使用智能功能之前，最好先验证上述要求。

有关日期表及其在 DAX 公式中的用法的更多详细信息，请参阅本教程库中的教程 = 使用 DAX 进行数据建模。

DAX 时间智能函数 – 类别

DAX 时间智能功能可分类如下 -

  • 返回单个日期的 DAX 函数。
  • 返回日期表的 DAX 函数。
  • DAX 函数计算一段时间内的表达式。

返回单个日期的 DAX 函数

此类别中的 DAX 函数返回单个日期。

此类别中有 10 个 DAX 函数 -

先生。 DAX 函数和返回值
1

FIRSTDATE（日期_列）

返回当前上下文中 Date_Column 中的第一个日期。
2

最后日期（日期_列）

返回当前上下文中 Date_Column 中的最后一个日期。
3

FIRSTNONBLANK（日期_列，表达式）

返回表达式具有非空值的第一个日期。
4

LASTNONBLANK（日期列，表达式）

返回表达式具有非空值的最后日期。
5

STARTOFMONTH（日期_列）

返回当前上下文中一个月的第一个日期。
6

ENDOFMONTH（日期_列）

返回当前上下文中一个月的最后一个日期。
7

季度开始时间（日期_列）

返回当前上下文中季度的第一个日期。
8

ENDOFQUARTER（日期_列）

返回当前上下文中季度的最后一个日期。
9

STARTOFYEAR（Date_Column，[YE_Date]）

返回当前上下文中一年的第一个日期。
10

ENDOFYEAR（日期列，[YE_日期]）

返回当前上下文中一年的最后一个日期。

返回日期表的 DAX 函数

此类别中的 DAX 函数返回日期表。这些函数主要用作 DAX 函数 CALCULATE 的 SetFilter 参数。

此类别中有 16 个 DAX 函数。其中八 (8) 个 DAX 函数是“上一个”和“下一个”函数。

  • “前一个”和“下一个”函数从当前上下文中的日期列开始，计算前一天或后一天、月份、季度或年份。

  • “上一个”函数从当前上下文中的第一个日期向后运行，“下一个”函数从当前上下文中的最后一个日期向前运行。

  • “上一个”和“下一个”函数以单列表的形式返回结果日期。

先生。 DAX 函数和返回值
1

前一天（日期_列）

返回一个表，其中包含一列所有日期，表示当前上下文中 Date_Column 中第一个日期之前的日期。
2

第二天（日期_列）

根据当前上下文中 Date_Column 中指定的第一个日期，返回一个表，其中包含第二天的所有日期的列。
3

上个月（日期_列）

基于当前上下文中 Date_Column 中的第一个日期，返回一个表，其中包含上个月的所有日期的列。
4

下个月（日期_列）

返回一个表，其中包含下个月的所有日期的列，基于当前上下文中 Date_Column 中的第一个日期。
5

上一季度（日期_列）

根据当前上下文中 Date_Column 中的第一个日期，返回一个表，其中包含上一季度的所有日期的列。
6

下季度（日期_列）

根据当前上下文中 Date_Column 中指定的第一个日期，返回一个表，其中包含下一季度的所有日期的列。
7

上一个（Date_Column，[YE_Date]）

返回一个表，其中包含上一年所有日期的列（给定当前上下文中 Date_Column 中的最后一个日期）。
8

NEXTYEAR（日期_列，[YE_日期]）

返回一个表，其中包含明年所有日期的列，基于当前上下文中 Date_Column 中的第一个日期。

四 (4) 个 DAX 函数计算一个时期内的一组日期。这些函数使用当前上下文中的最后日期执行计算。

先生。 DAX 函数和返回值
1

DATESMTD（日期_列）

返回一个表，其中包含当前上下文中本月至今的日期列。
2

DATESQTD（日期_列）

返回一个表，其中包含当前上下文中本季度至今的日期列。
3

DATESYTD（日期列，[YE_日期]）

返回一个表，其中包含当前上下文中今年至今的日期列。
4

SAMEPERIODLASTYEAR（日期_列）

返回一个表，其中包含当前上下文中指定 Date_Column 中的日期向后移动一年的日期列。

注意- SAMEPERIODLASTYEAR 要求当前上下文包含一组连续的日期。

如果当前上下文不是连续的日期集，则 SAMEPERIODLASTYEAR 将返回错误。

  • 四 (4) 个 DAX 函数用于从当前上下文中的日期集转换为新的日期集。

    这些 DAX 函数比以前的函数更强大。

    • DAX 函数 – DATEADD、DATESINPERIOD 和 PARALLELPERIOD 从当前上下文中移动一定数量的时间间隔。间隔可以是日、月、季度或年，分别用关键字 DAY、MONTH、QUARTER 和 YEAR 表示。

      例如：

  • 向后平移2天。

  • 向前推进5个月。

  • 从今天开始向前推进一个月。

  • 回到去年的同一季度。

      如果函数参数 - 间隔数（整数值）为正，则向前移动，如果为负，则向后移动。

    • DAX 函数 – DATESBETWEEN 计算指定开始日期和结束日期之间的日期集。

先生。 DAX 函数和返回值
1

DATEADD（日期列、间隔数、间隔）

返回一个包含一列日期的表，这些日期在时间上从当前上下文中的日期向前或向后移动指定的间隔数。
2

DATESINPERIOD（日期列、开始日期、间隔数、间隔）

返回一个表，其中包含一列以 start_date 开始并持续指定 number_of_intervals 的日期。
3

PARALLELPERIOD（日期列、间隔数、间隔）

返回一个表，其中包含一列日期，该列表示与当前上下文中指定 Date_Column 中的日期平行的时间段，其中日期在时间上向前或向后移动了多个间隔。
4

DATESBETWEEN（日期列、开始日期、结束日期）

返回一个表，其中包含一列从 start_date 开始一直到 end_date 的日期。

在一段时间内计算表达式的 DAX 函数

此类别中的 DAX 函数评估指定时间段内的表达式。

此类别中有九 (9) 个 DAX 函数 -

  • 此类别中的三 (3) 个 DAX 函数可用于计算指定时间段内的任何给定表达式。

先生。 DAX 函数和返回值
1

TOTALMTD（表达式、日期列、[SetFilter]）

在当前上下文中计算本月至今的日期表达式的值。
2

TOTALQTD（表达式、日期列、[SetFilter]）

在当前上下文中计算本季度至今的日期表达式的值。
3

TOTALYTD（表达式、Date_Column、[SetFilter]、[YE_Date]）

在当前上下文中计算年初至今的日期表达式的值

  • 此类别中的六 (6) 个 DAX 函数可用于计算期初和期末余额。

    • 任何期间的期初余额与上期期末余额相同。

    • 期末余额包括截至期末的所有数据，而期初余额不包括当期的任何数据。

    • 这些 DAX 函数始终返回针对特定时间点计算的表达式的值。

  • 我们关心的时间点始终是日历周期中最后一个可能的日期值。

  • 期初余额以上期最后一日为准，期末余额以本期最后一日为准。

  • 当前期间始终由当前日期上下文中的最后一个日期确定。

先生。 DAX 函数和返回值
1

OPENINGBALANCEMONTH（表达式、Date_Column、[SetFilter]）

计算当前上下文中该月第一天的表达式。
2

CLOSINGBALANCEMONTH（表达式，Date_Column，[SetFilter]）

计算当前上下文中该月最后一个日期的表达式。
3

OPENINGBALANCEQUARTER（表达式、Date_Column、[SetFilter]）

在当前上下文中评估季度第一天的表达式。
4

CLOSINGBALANCEQUARTER（表达式、Date_Column、[SetFilter]）

评估当前上下文中季度最后一天的表达式。
5

OPENINGBALANCEYEAR（表达式、Date_Column、[SetFilter]、[YE_Date]）

计算当前上下文中一年的第一个日期的表达式。
6

CLOSINGBALANCEYEAR（表达式、Date_Column、[SetFilter]、[YE_Date]）

计算当前上下文中一年的最后一个日期的表达式。