Excel DAX - 时间智能
DAX 有一个重要而强大的功能，称为时间智能。 时间智能使您能够编写引用数据透视表中使用的时间段的 DAX 公式。
DAX 有 35 个时间智能函数，专门用于聚合和比较一段时间内的数据。 但是，这些 DAX 函数对数据有一些限制，您需要了解并谨慎操作以避免错误。
为什么时间智能让 DAX 变得强大?
时间智能函数处理不断变化的数据，具体取决于您在数据透视表和 Power View 可视化中选择的上下文。 如您所知，大多数数据分析涉及汇总不同时间段的数据、比较不同时间段的数据值、了解趋势并根据未来预测做出决策。
例如，您可能希望按产品对上个月的销售额求和，并将总计与会计年度中其他月份的总计进行比较。 这意味着，您必须使用日期来分组和汇总特定时间段内的销售交易。
在这里您可以看到 DAX 的强大功能。 您可以使用 DAX 时间智能函数来定义计算字段，以帮助您随时间分析数据，而无需更改数据透视表中的日期选择。 这使您的工作更轻松。 此外，您可以构建数据透视表，这是其他任何方式都不可能实现。
DAX 时间智能函数的要求
DAX 时间智能功能有一定的要求。 如果不满足这些要求，您可能会遇到错误或者它们可能无法正常工作。 因此，您也可以将这些要求称为规则或约束。 以下是某些 DAX 时间智能功能要求/规则/约束 −
您的数据模型中需要有一个日期表。
日期表必须包含一个被 DAX 视为日期列的列。 您可以按照自己的方式命名该列，但它应符合以下条件：日期列应包含一组连续的日期，涵盖您正在分析数据的时间段内的每一天。
每个日期在日期列中必须且仅存在一次。
您不能跳过任何日期（例如，您不能跳过周末日期）。
DAX 时间智能函数仅适用于标准日历，假设年初为 1 月 1 日，年末为 12 月 31 日，日历年中的月份和每个月的天数。
但是，您可以为不同的财政年度定制一个标准日历。 在任何时候使用智能功能之前验证上述要求是一个很好的做法。
有关日期表及其在 DAX 公式中的用法的更多详细信息，请参阅本教程库中的章节 = 使用 DAX 进行数据建模。
DAX 时间智能函数 – 类别
DAX时间智能函数可以分为以下几类 −
- 返回单个日期的 DAX 函数。
- 返回日期表的 DAX 函数。
- 在一段时间内评估表达式的 DAX 函数。
返回单个日期的 DAX 函数
此类别中的 DAX 函数返回单个日期。
此类别中有 10 个 DAX 函数 −
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | FIRSTDATE (Date_Column) 返回当前上下文中 Date_Column 中的第一个日期。 |
| 2 | LASTDATE (Date_Column) 返回当前上下文中 Date_Column 中的最后一个日期。 |
| 3 | FIRSTNONBLANK (Date_Column, Expression) 返回表达式具有非空值的第一个日期。 |
| 4 | LASTNONBLANK (Date_Column, Expression) 返回表达式具有非空值的最后日期。 |
| 5 | STARTOFMONTH (Date_Column) 返回当前上下文中一个月的第一个日期。 |
| 6 | ENDOFMONTH（Date_Column） 返回当前上下文中一个月的最后一个日期。 |
| 7 | STARTOFQUARTER（Date_Column） 返回当前上下文中季度的第一个日期。 |
| 8 | ENDOFQUARTER (Date_Column) 返回当前上下文中季度的最后日期。 |
| 9 | STARTOFYEAR (Date_Column, [YE_Date]) 返回当前上下文中一年的第一个日期。 |
| 10 | ENDOFYEAR (Date_Column, [YE_Date]) 返回当前上下文中一年的最后一个日期。 |
返回日期表的 DAX 函数
此类别中的 DAX 函数返回一个日期表。 这些函数将主要用作 DAX 函数 - CALCULATE 的 SetFilter 参数。
此类别中有 16 个 DAX 函数。 这些 DAX 函数中的八 (8) 个是"上一个"和"下一个"函数。
"上一个"和"下一个"函数从当前上下文中的日期列开始，计算前一天或后一天、一个月、一个季度或一年。
"上一个"函数从当前上下文中的第一个日期开始倒退，"下一个"函数从当前上下文中的最后一个日期向前移动。
"上一个"和"下一个"函数以单个列表的形式返回结果日期。
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | PREVIOUSDAY (Date_Column) 返回一个表，其中包含一列所有日期，表示当前上下文中 Date_Column 中第一个日期之前的那一天。 |
| 2 | NEXTDAY (Date_Column) 根据当前上下文中 Date_Column 中指定的第一个日期，返回包含第二天所有日期列的表。 |
| 3 | PREVIOUSMONTH (Date_Column) 根据当前上下文中 Date_Column 中的第一个日期，返回包含上个月所有日期列的表。 |
| 4 | NEXTMONTH (Date_Column) 根据当前上下文中 Date_Column 中的第一个日期，返回包含下个月所有日期列的表。 |
| 5 | PREVIOUSQUARTER (Date_Column) 根据当前上下文中 Date_Column 中的第一个日期，返回包含上一季度所有日期列的表。 |
| 6 | NEXTQUARTER（Date_Column） 根据当前上下文中 Date_Column 中指定的第一个日期，返回包含下一季度所有日期列的表。 |
| 7 | 上一年（Date_Column，[YE_Date]） 返回一个表，其中包含前一年的所有日期列，给定当前上下文中 Date_Column 中的最后一个日期。 |
| 8 | NEXTYEAR (Date_Column, [YE_Date]) 根据当前上下文中 Date_Column 中的第一个日期，返回包含下一年所有日期列的表。 |
四 (4) 个 DAX 函数计算一个时期内的一组日期。 这些函数使用当前上下文中的最后日期执行计算。
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | DATESMTD (Date_Column) 返回一个表，其中包含当前上下文中本月至今的日期列。 |
| 2 | DATESQTD (Date_Column) 返回一个表，其中包含当前上下文中本季度至今的日期列。 |
| 3 | DATESYTD (Date_Column, [YE_Date]) 返回一个表，其中包含当前上下文中年初至今的日期列。 |
| 4 | SAMEPERIODLASTYEAR (Date_Column) 返回一个表，其中包含在当前上下文中从指定 Date_Column 中的日期向后移动一年的日期列。 注意− SAMEPERIODLASTYEAR 要求当前上下文包含一组连续的日期。 如果当前上下文不是一组连续的日期，则 SAMEPERIODLASTYEAR 将返回错误。 |
四 (4) 个 DAX 函数用于从当前上下文中的日期集转移到新日期集。
这些 DAX 函数比之前的函数更强大。
DAX 函数 – DATEADD、DATESINPERIOD 和 PARALLELPERIOD 从当前上下文中移动一定数量的时间间隔。 时间间隔可以是日、月、季或年，分别用关键词DAY、MONTH、QUARTER和YEAR表示。
例如：
向后移动 2 天。
向前推进 5 个月。
从今天开始向前推进一个月。
回到去年的同一季度。
DAX 函数 – DATESBETWEEN 计算指定开始日期和结束日期之间的日期集。
如果函数参数-间隔数（整数值）为正，则向前移动，如果为负，则向后移动。
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | DATEADD (Date_Column, Number_of_Intervals, Interval) 返回包含一列日期的表，从当前上下文中的日期向前或向后移动指定数量的时间间隔。 |
| 2 | DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval) 返回一个表，其中包含一列日期，该日期以 start_date 开始并持续指定的 number_of_intervals。 |
| 3 | PARALLELPERIOD (Date_Column, Number_of_Intervals, Interval) 返回一个表，其中包含一列日期，该日期表示与当前上下文中指定 Date_Column 中的日期平行的时期，日期在时间上向前或向后移动了多个间隔。 |
| 4 | DATESBETWEEN (Date_Column, Start_Date, End_Date) 返回一个表，其中包含一列从 start_date 开始一直持续到 end_date 的日期。 |
在一段时间内评估表达式的 DAX 函数
此类别中的 DAX 函数计算指定时间段内的表达式。
此类别中有九 (9) 个 DAX 函数 −
此类别中的三(3) 个 DAX 函数可用于评估指定时间段内的任何给定表达式。
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | TOTALMTD (Expression, Date_Column, [SetFilter]) 在当前上下文中计算当月至今日期的表达式值。 |
| 2 | TOTALQTD (Expression, Date_Column, [SetFilter]) 在当前上下文中评估本季度至今的日期表达式的值。 |
| 3 | TOTALYTD (Expression, Date_Column, [SetFilter], [YE_Date]) 在当前上下文中计算今年迄今日期的表达式值 |
此类别中的六 (6) 个 DAX 函数可用于计算期初和期末余额。
任何一期的期初余额与上一期的期末余额相同。
期末余额包括截至期末的所有数据，而期初余额不包括本期的任何数据。
这些 DAX 函数始终返回针对特定时间点计算的表达式的值。
我们关心的时间点始终是日历周期中最后一个可能的日期值。
期初余额以上期末日为准，期末余额以本期末日为准。
当前期间始终由当前日期上下文中的最后日期确定。
| 序号 | DAX 函数 & 返回值 |
|---|---|
| 1 | OPENINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) 在当前上下文中的月份的第一个日期计算表达式。 |
| 2 | CLOSINGBALANCEMONTH (Expression, Date_Column, [SetFilter]) 在当前上下文中的月份的最后一天计算表达式。 |
| 3 | OPENINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) 在当前上下文中计算季度第一天的表达式。 |
| 4 | CLOSINGBALANCEQUARTER (Expression, Date_Column, [SetFilter]) 在当前上下文中计算季度最后日期的表达式。 |
| 5 | OPENINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) 在当前上下文中计算一年中第一天的表达式。 |
| 6 | CLOSINGBALANCEYEAR (Expression, Date_Column, [SetFilter], [YE_Date]) 在当前上下文中计算一年中最后一个日期的表达式。 |
