[知识体系] 日期区间函数 DATESBETWEEN、DATESINPERIOD、SAMEPERIODLASTYEAR和PARALL...

  [复制链接]
查看145832 | 回复138 | 2021-2-21 18:54:03 | 显示全部楼层 |阅读模式
本帖最后由 XF 于 2021-2-22 14:20 编辑

时间智能函数中有一组用来返回日期区间的函数,本文为你介绍其中最常用的四个 DATESBETWEENDATESINPERIODSAMEPERIODLASTYEARPARALLELPERIOD。它们根据不同的逻辑返回特定的日期区间,但是彼此有一个共同点,都必须使用日期表的日期列作为参数,参照时间智能第一篇文章中对日期表的介绍,这个日期列必须是连续且完整的。

DATESBETWEEN

返回一张单列形式的表,其中包含两个给定日期之间的所有日期。

参数属性描述
Dates引用的日期列
StartDate开始日期
EndDate结束日期

注意
  • 如果 StartDate 为空,将使用日期列中最早的值。
  • 如果 EndDate 为空,将使用日期列中的最新值。
  • 起始日期和结束日期都包括在返回的表中:也就是说,如果销售发生在 9 月 1 日并且这一天被用作开始日期,那么 DATESBETWEEN 将计算 9 月 1 日的销售。
  • 如果 StartDate 大于 EndDate,结果是为空。

示例用法

以下公式计算 2007 年夏季的销售额

  1. CALCULATE (
  2.     SUM ( InternetSales_USD[SalesAmount_USD] ),
  3.     DATESBETWEEN ( DateTime[DateKey], DATE ( 2007, 6, 1 ), DATE ( 2007, 8, 31 ) )
  4. )
复制代码

DATESINPERIOD
  1. DATESINPERIOD ( <Dates>, <StartDate>, <NumberOfIntervals>, <Interval> )
复制代码


返回给定区间中的所有日期组成的单列形式的表,该函数应用偏移量(<NumberOfIntervals>)到<Dates>所在的日期列,返回<Interval>所指定区间中包含的所有日期。

参数属性描述
Dates引用的日期列
StartDate开始日期
NumberOfIntervals间隔数量,负数代表过去,正数代表将来
Interval间隔类型:年、季度、月或日

示例用法

以下公式计算当前筛选上下文中最近一年的销售额

  1. [MAT Sales] :=
  2. CALCULATE (
  3.     [Sales Amount],
  4.     DATESINPERIOD (
  5.         'Date'[Date],
  6.         LASTDATE ( 'Date'[Date] ),
  7.         -1,
  8.         YEAR
  9.     )
  10. )
复制代码

DATESINPERIOD vs DATESBETWEEN

两个函数都返回包含日期区间的表,不同之处在于:

  • DATESINPERIOD 支持按自定义的时间间隔返回日期,包括 DAY、MONTH、QUARTER、YEAR。
  • DATESBETWEEN 只能返回两个指定日期间的时间间隔。
  • DATESBETWEEN 包含首尾日期,DATESINPERIOD 的首尾日期服从区间设定,向前统计(第三参数小于 0)时,不包含起始端日期;向后统计(第三参数大于 0)时,不包含结束端日期

  1. DATESINPERIOD('date'[Date],DATE(2018,2,1),1,DAY)     // 返回 2018 年 2 月 1 日
  2. DATESINPERIOD('date'[Date],DATE(2018,2,1),1,MONTH)   // 返回 2018 年 2 月 1 日至 2018 年 2 月 28 日,不含 3 月 1 日
  3. DATESINPERIOD('date'[Date],DATE(2018,2,1),-1,MONTH)  // 返回 2018 年 1 月 2 日至 2018 年 2 月 1 日,不含 1 月 1 日
  4. DATESINPERIOD('date'[Date],DATE(2018,2,1),1,QUARTER) // 返回 2018 年 2 月 1 日至 2018 年 4 月 30 日
  5. DATESINPERIOD('date'[Date],DATE(2018,2,1),1,YEAR)    // 返回 2018 年 2 月 1 日至 2019 年 1 月 31 日,不含 19 年 2 月 1 日
复制代码

SAMEPERIODLASTYEAR
  1. SAMEPERIODLASTYEAR ( <Dates> )
复制代码


SAMEPERIODLASTYEAR 返回单列的日期表,其中包含当前筛选上下文中的日期向过去平移一年的日期值。SAMEPERIODLASTYEAR 常用于计算同比移动平均,与下面的写法等价

  1. DATEADD(<Dates>, -1, YEAR)
复制代码

示例用法

以下公式计算零售商前一年的销售额

  1. CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[Date]))
复制代码

不完整的日期列导致的异常结果

我们知道作为日期表的日期列必须使用连续且完整的日期,其中完整指的是完整的年,比如对于日历年必须以 1 月 1 日开始,12 月 31 日结束,财年也是同理。如果不遵循这个规则,某些特殊情况下可能得到异常结果:

  1. SAMEPERIODLASTYEAR(DATESBETWEEN('date'[Date],DATE(2019,1,1),DATE(2019,1,20)))
复制代码


通常我们认为上面这个公式返回 2018 年 1 月 1 日到 2018 年 1 月 20 日的所有日期,在正常情况下确实是这样,但实际上它也可以返回 2018 年 1 月 1 日到2018 年 1 月 31 日的所有日期。原因在于:

游客,如果您要查看本帖隐藏内容请回复


            
         
PARALLELPERIOD
  1. PARALLELPERIOD ( <Dates>, <NumberOfIntervals>, <Interval> )
复制代码


根据当前上下文中的日期和给定的时间间隔,返回一组并行的完整日期周期。

参数属性描述
Dates引用的日期列
NumberOfIntervals间隔数量,负数代表过去,正数代表将来
Interval间隔类型:年、季度、月

原理

PARALLELPERIOD 接受日期列在当时筛选上下文中的结果,将第一个日期和最后一个日期按指定的间隔数量进行平移,返回平移后两日期之间的所有连续日期。如果筛选上下文中的是不完整的月、季度或年,那么结果中缺失的月份也将被填充,以保证完整的时间间隔。

PARALLELPERIOD vs DATEADD



PARALLELPERIOD 函数与 DATEADD 函数类似,都是对给定的日期进行平移。不同之处在于 PARALLELPERIOD 总是返回给定粒度级别的完整周期,而不是 DATEADD 返回的部分周期。例如,选定 6 月 10 日到 6 月 21 日的日期区间,你需要将其向未来平移一个月,PARALLELPERIOD 将返回下个月的所有日期(7 月 1 日到 7 月 31 日);而 DATEADD 将只包含 7 月 10 日到 7 月 21 日的日期。
回复

使用道具 举报

kbcts | 2021-4-24 14:00:20 来自手机 | 显示全部楼层
路过 帮顶 嘿嘿
回复

使用道具 举报

kinsaang | 2021-4-29 16:31:14 | 显示全部楼层
支持,赞一个
回复

使用道具 举报

蹓蹓女侠 | 2021-5-24 12:37:43 来自手机 | 显示全部楼层
呵呵,明白了
回复

使用道具 举报

盈盈 | 2021-6-17 21:55:04 来自手机 | 显示全部楼层
这么强,支持楼主,佩服
回复

使用道具 举报

dabofeng | 2021-6-30 21:02:27 来自手机 | 显示全部楼层
老大,这个说的很好,这个真不错!
回复

使用道具 举报

fivemeteor | 2021-10-1 09:23:24 | 显示全部楼层
前排支持下了哦~
回复

使用道具 举报

37度3 | 2021-10-27 12:27:50 | 显示全部楼层
very good。。。。。
回复

使用道具 举报

胖嘟嘟 | 2021-12-14 14:07:55 | 显示全部楼层
支持,赞一个
回复

使用道具 举报

marco | 2022-1-29 13:32:25 | 显示全部楼层
支持一下云发教育!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则