DATEADD 最常用的时间智能函数之一，所有对日期的平移都需要用到它。
初识 DATEADD
- DATEADD ( <Dates>, <NumberOfIntervals>, <Interval> )
| 参数 | 属性 | 描述 | | Dates | | 包含日期的单列或包含日期的单列形式的表 | | NumberOfIntervals | | 偏移量 | | Interval | | 偏移单位: Day, Month, Quarter, Year. |
DATEADD 用于获取经过特定偏移后的一组日期集合，它是一个表函数，只处理作为第一参数传递的日期列中的现有值。DATEADD 在特定条件下会应用一些特殊的逻辑，特别是与月份选择相关的时候，你将在稍后的案例中了解这一点。
DATEADD 使用作为第一参数的日期列的值，它忽略日期表的其他列，因此它必须从当前可用的日期值中提取其他时间信息（如年份、季度和天）。考虑以下公式：
- DATEADD ( 'Date'[Date], -1, MONTH )
它返回当前日期区间在上一个月对应的所有日期，我们可以写出两种最接近（但不正确）DAX 公式：
- FILTER (
- ALL ( 'Date'[Date] ),
- CONTAINS (
- VALUES ( 'Date'[Date] ),
- 'Date'[Date], DATE ( YEAR ( 'Date'[Date] ), MONTH ( 'Date'[Date] ) + 1, DAY ( 'Date'[Date] ) )
- )
- )
- VAR OffsetMonth = -1
- RETURN
- TREATAS (
- SELECTCOLUMNS (
- CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ),
- "Date", DATE ( YEAR ( 'Date'[Date] ), MONTH ( 'Date'[Date] ) + OffsetMonth, DAY ( 'Date'[Date] ) )
- ),
- 'Date'[Date]
- )
在前面的示例和本章的其他公式中，我们使用了 CONTAINS 函数，它的作用是，如果作为第一参数的表至少有一行，满足第二参数指定的列值与第三参数的表达式相对应，则返回 True。你可以在“查找匹配函数”一章中找到有关此函数的完整描述。
这个公式是不正确的，因为它试图通过从当前月份中减去 1 来找到前一个月中对应的天数。但是，如果计算从 1 月份开始，则应该减少年份值，并将 Month 参数设为 12。一种改善后的写法是：
- FILTER (
- ALL ( 'Date'[Date] ),
- CONTAINS (
- VALUES ( 'Date'[Date] ),
- 'Date'[Date], DATE ( YEAR ( 'Date'[Date] )
- + IF (
- MONTH ( 'Date'[Date] ) = 12,
- 1,
- 0
- ), IF (
- MONTH ( 'Date'[Date] ) = 12,
- 1,
- MONTH ( 'Date'[Date] ) + 1
- ), DAY ( 'Date'[Date] ) )
- )
- )
这个版本的公式在 1 月至 12 月期间正常计值，并且仅适用于负偏移量。但是, 我们需要更改 DAX 表达式以处理-1 以外的偏移量 (例如， 你可能使用-2 返回两个月之后的日期) ，而且这种写法还不能正确处理具有不同天数的月份之间的移动。事实上，如果目标月份的天数较少，那么最后这个实现方案效果很好，但如果你从 2 月移动到 1 月，就会丢失 2-3 天，具体丢失的天数取决于年份。
此时，如果你不想依赖于日期表中的其他列，那么在 DAX 中实现逻辑就会复杂得多。在本章最后的自定义时间智能一文中，你将看到一种更简单、更灵活的方法，可以基于日期表的内容在 DAX 中实现时间智能。现在，让我们看看 DATEADD 的确切行为是什么。
深入理解 DATEADD
DATEADD 是一个常用函数，其他的一些时间智能函数会在内部调用 DATEADD，因为日期的偏移计算存在一些复杂性，导致这个函数有一些特殊之处，理解这些特殊的行为可以帮助你彻底搞懂 DATEADD：
- 规则 1：DATEADD 只适用于连续日期选择，否则会引发错误
- 规则 2：DATEADD 返回作为第一参数的时间列中的日期
- 规则 3：当平移操作后的相应月份中不存在相应的日期时，DATEADD 的结果包括相应月份的最后一天
- 规则 4：当选择中包含一个月的最后一天，且选择的跨度超过一天时，DATEADD 的结果包含从偏移月份的对应日期到偏移月份结束的所有天数
以下示例有助于理解这些行为的影响，定义以下度量值：Days 统计当前天数，PM Days 统计 DATEADD 平移一个月得到的天数，PM Day 在结果只包含一天时，返回日期。
- [Days] := COUNTROWS ( 'Date' )
- [PM Days] := COUNTROWS ( DATEADD ( 'Date'[Date], -1, MONTH ) )
- [PM Day] := IF ( HASONEVALUE ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ))
规则 1：必须选择连续日期
2020 年 4 月更新：DATEADD 已经移除了此项限制，你可以在 Power BI Desktop 中选择任意不连续的日期都不会影响公式计值。但是在 Excel 2013 和 2016 版本的 Power Pivot 中，此规则可能仍然生效。
如果你试图选择 2008 年 1 月和 2008 年 3 月，而没有选择 2 月，你会得到以下错误:
函数”DATEADD”仅适用于连续的日期选择
使用过 DATEADD 的人可能都遇到过这个让人困惑的提示，严格来讲这不是错误（出处），是为了避免日期不连续导致结果存在歧义而故意为之，因为只有包含连续日期的表在平移之后才能同样得到连续日期，让我们用一个示例来说明：
使用所有二月份日期通过 DATEADD 整体向后平移一个月，得到的是三月份的所有日期，这是 DATEADD 的默认行为，但如果你从二月中任意去掉一天，这就构成了一个不连续的日期选择，此时平移后的结果应该有多少天？是 31-1 还是 28-1？并且新的日期区间里应该移除哪一天才能与之前的区间对应？这就是产生歧义的原因。所以为了避免类似情况发生，DATEADD 中禁止不连续的日期选择。
移除 2 月 27 日后，平移到 3 月份的日期应该如何处理？
如果需要在时间智能计算中处理非连续日期，那么你必须编写自定义 DAX 计算，后面的“非连续周期计算”一文将对此进行解释。
规则 2：只返回第一参数中存在的日期
此规则在所选日期接近日期表的日期临界点时有效，例如，选择 2008 年 1 月的前四天，结果返回 2007 年 12 月的前四天。如图 7-35 所示，在本例中，PM Days 的结果对应于 Days，因为此时不适用特殊规则。
图 7-35 当前日期平移一月
本例中使用的日期表包含从 2005 到 2011 年的所有日期，如果初始选择为 2005 年 1 月，则结果为空，因为日期表没有 2004 年 12 月的值。你可以在图 7-36 中看到这种行为。
图 7-36 当返回的日期不在日期表中时，DATEADD 结果为空
日期表应该包括一年中的所有天数的主要原因是 DATEADD 的这种行为。请记住，DAX 中的多个时间智能函数在内部使用 DATEADD，因此拥有完整的日期表是正确执行 DAX 时间智能的基础
规则 3：智能匹配
规则 3 与月份之间的不同天数有关。例如，如果选择 2008 年 7 月 31 日，结果是 2008 年 6 月 30 日，如图 7-37 所示。
图 7-37 在目标月份中不存在的日期被月份的最后一天替换
这个规则的结果是，你可能会得到比初始选择更少的天数。这是很直观的，选择 3 月份的 31 天会导致 2 月份的 28 天或 29 天被选择(取决于年份)。然而，规则 3 的作用是，每天都有相应的日期，所以你总是将一个月的最后一天与相应月份的最后一天进行比较。从图 7-38 可以看出，从 3 月 29 日到 3 月 31 日的每一天都对应着 2 月 29 日。在月级别，最初选择的三天(参考 days 列)对应的结果只有一天(参考 PM days)，因为 DATEADD 不返回重复日期。
图 7-38 开始时选择多天可能会导致 DATEADD 的结果是同一天
规则 4：跨临界日期的计算逻辑
当选择的日期范围包含一个月的最后一天时，此规则会生成不同的行为。例如，考虑开始时选择的两天是 2008 年 6 月 30 日和 2008 年 7 月 1 日。把日期推迟一个月会有什么结果呢？对单独的日期而言，结果是前一个月的对应日期(分别为 2008 年 5 月 30 日和 2008 年 6 月 1 日)。但是，当你选择所有日期时，结果还将包含第一天和最后一天之间的日期，在这个案例中，这个新增的日期是 2008 年 5 月 31 日。在图 7-39 中可以看到，当你查看行 CY 2008 时，PM Days 度量值返回的值是 3 而不是 2。
图 7-39 DATEADD 的结果包含选择的第一天和最后一天之间的所有日期
月份级别的结果很有趣：即使你选择了月份的最后一天(2008 年 6 月 30 日)，结果也只有 2008 年 5 月 30 日。在这种情况下，你无法将一个月的最后一天与前一个月的最后一天进行比较。在图 7-40 中可以看到，Days 和 PM Days 的值在日级别和月级别都是 1。然而，如果在这个月的选择包括至少两天，那么规则 4 的效果是增加对 2008 年 5 月 31 日的选择。这只在 PM Days 中可见，在 June 2008 和 CY 2008 的行中为 3。
图 7-40 在一个月中选择两天或更多天，其中包括当月的最后一天, 可能会通过将天数添加到月底来延长结果
规则 4 的一个极端例子是，当你选择非闰年的 2 月 27 日和 2 月 28 日时，PM Days 结果将从两天延长到五天，如图 7-41 所示。
图 7-41 PM Days 显示选择 2 月份的 2 天结果是 1 月份的 5 天
这些规则的结果是在月级别操作时提供直观的行为。如图 7-42 所示，当你比较月份级别的选择时，结果是直观且可预期的：显示上月的天数。了解本节中描述的规则对于处理在月内选择部分日期时可能发生的副作用非常重要。我们使用天数只是为了明确函数的这种行为，但你肯定会使用其他度量值 (例如，数量或销售金额)，以简化两个时间段 (在本例中为上月) 之间的值的比较。
图 7-42 PM Days 显示了上个月的天数
DATEADD.xlsx
(4.54 MB, 下载次数: 0, 售价: 3 金钱)
|
[复制链接]
