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] )
这个公式是不正确的,因为它试图通过从当前月份中减去 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 中禁止不连续的日期选择。
如果需要在时间智能计算中处理非连续日期,那么你必须编写自定义 DAX 计算,后面的“非连续周期计算”一文将对此进行解释。
上一个评论不知道怎么又设置成私密了,只好重新开一个,麻烦高老师把上一条给删了,谢谢了!
高老师,在《DAX权威指南》书中P250 中描述的DATEADD使用的第3条规则:“如果选择中包括一个月的最后两天,那么DAIEADD的结果中会包括平移后的月份对应的日期到此月份结束日期之间的所有天数。” 与这里描述的规则4:“当选择中包含一个月的最后一天,且选择的跨度超过一天时,DATEADD 的结果包含从偏移月份的对应日期到偏移月份结束的所有天数。”貌似不太一样。
而且书中对规则3的举例说明(P252~253)中对规则3的说明:“有这种同时包含6月29日和30日的选择结果才会在平移后包括上—个月的最后一天(5月31日)”。
经测试,我觉得网络上的《DAX圣经》描述似乎要准确一些!
这里只有日级别是1, 月级别是3
老师,为什么要外套一个calculatetable呢,如果不加的话有影响吗?
老师,图中第十三行,按照文中的意思应该是+1才对,不然意义就不同了
老师,这里不应该是提前一个月么