从数据到信息
从信息到洞察

日期区间函数 DATESBETWEEN、DATESINPERIOD、SAMEPERIODLASTYEAR和PARALLELPERIOD

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

DATESBETWEEN

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

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

注意

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

示例用法

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

CALCULATE (
    SUM ( InternetSales_USD[SalesAmount_USD] ),
    DATESBETWEEN ( DateTime[DateKey], DATE ( 2007, 6, 1 ), DATE ( 2007, 8, 31 ) )
)

DATESINPERIOD

DATESINPERIOD ( <Dates>, <StartDate>, <NumberOfIntervals>, <Interval> )

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

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

示例用法

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

[MAT Sales] :=
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -1,
        YEAR
    )
)

DATESINPERIOD vs DATESBETWEEN

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

  1. DATESINPERIOD 支持按自定义的时间间隔返回日期,包括 DAY、MONTH、QUARTER、YEAR。
  2. DATESBETWEEN 只能返回两个指定日期间的时间间隔。
  3. DATESBETWEEN 包含首尾日期,DATESINPERIOD 的首尾日期服从区间设定,向前统计(第三参数小于 0)时,不包含起始端日期;向后统计(第三参数大于 0)时,不包含结束端日期
DATESINPERIOD('date'[Date],DATE(2018,2,1),1,DAY)     // 返回 2018 年 2 月 1 日
DATESINPERIOD('date'[Date],DATE(2018,2,1),1,MONTH)   // 返回 2018 年 2 月 1 日至 2018 年 2 月 28 日,不含 3 月 1 日
DATESINPERIOD('date'[Date],DATE(2018,2,1),-1,MONTH)  // 返回 2018 年 1 月 2 日至 2018 年 2 月 1 日,不含 1 月 1 日
DATESINPERIOD('date'[Date],DATE(2018,2,1),1,QUARTER) // 返回 2018 年 2 月 1 日至 2018 年 4 月 30 日
DATESINPERIOD('date'[Date],DATE(2018,2,1),1,YEAR)    // 返回 2018 年 2 月 1 日至 2019 年 1 月 31 日,不含 19 年 2 月 1 日

SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR ( <Dates> )

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

DATEADD(<Dates>, -1, YEAR)

示例用法

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

CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[Date]))

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

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

SAMEPERIODLASTYEAR(DATESBETWEEN('date'[Date],DATE(2019,1,1),DATE(2019,1,20)))

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

公众号二维码加载失败时的替代文字
此处内容已经被作者无情的隐藏,请输入验证码查看内容
验证码:
请关注“PowerBI极客”公众号,回复关键字“完整”,获取验证码。 【注】手机扫描二维码快速关注“PowerBI极客”官方公众号。

PARALLELPERIOD

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 日的日期。

13
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
4 评论数
9 被回复的评论
8 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
似水无痕

高老师,图片中用的是 DateTime[DateKey] 列,没有下载到您的文件,不知道 [DateKey]列是不是日期格式列,感觉应该用 DateTime[Date] 才合适

01.png
游客
芒果

RangeOfDates =
DATESBETWEEN (
‘Date'[Date],
Sales[Order Date],
Sales[Delivery Date]
)

高老师,我想用 DATESBETWEEN 函数产生订单日期到发货日期之间的日期表。请问怎么产生?针对SALES表。想了半天没有合适的函数可以实现。用如下形式好像也不对:
Measure = FILTER(Sales,
DATESBETWEEN(‘Date'[Date],’Sales'[Order Date],’Sales'[Delivery Date])
)

我就想用SALE表里的订单日期和发货日期作为datebetween的参数来生成一个日期表。请指教。

成员
xianwensu

老师,所有时间智能函数,如果日期列参数等价于CALCULATETABLE(DISTINCT([date])),都会有上下文转换功能,如果有外部行上下文,一旦执行了外部上下文转化,都会隐藏外部筛选上下文吗?此类函数包括 Dateadd, PARALLELPERIOD,lastdate, firstdate 等等等等

成员
xifeng

老师,DATESBETWEEN和DATESINPERIOD的第一个参数,是不是隐性使用了ALL?,例如,DATESBETWEEN等价于:
FILTER(ALL([日期列]),[日期列]>=开始日期 && [日期列]<=结束日期 )

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数