在数据分析过程中,经常需要从一个数据推到另外一个数据,日期数据也是如此,需要从一个日期推到另外一个相关的日期,或者从一群日期推到另外一个相关的日期/一群相关的日期。这一期说的就是日期之间彼此推衍的函数,会比之前复杂不少。
第一部分
先用 1. DAX 时间函数--生成日期表_monthno是什么函数-CSDN博客 中提到的 CALENDAR 函数生成日期表。
1) DATEADD 函数:
返回一个单列的日期表,将当前筛选上下文中的日期按指定的间隔向未来或者过去平移。
该函数一看是一个非常有用的函数,事实上它非常鸡肋,非常鸡肋。
语法:DATEADD ( <日期列>, <偏移量>, <偏移单位> )
日期列: DATE 格式的数据列,请注意网上有一句话 :"如果日期列中的数据不是连续间隔,则函数回返回错误"???What,这么设计是为啥,我完全不明白! 现实世界的数据没有这么完美的! 以上是微软官方网站的解释,不死心的我决定直接试试,我生成了一个不连续的数据表 sheet1,待会我们试试。
偏移量: 偏移的数量,是一个数字标量。 指定了正数,则日期列中的日期向未来推移;如果指定的数字为负数,则日期列中的日期向过去推移 。
偏移单位:可以是年 YEAR、月 MONTH、季度 QUARTER、日 DAY
但是请一定注意,下面的用法是错误的,因为 DATEADD 返回的是表!!! DATEADD 返回的是表!!!DATEADD 返回的是表!!!,不能产生新列!
这么设计是为啥,我也完全不明白! ,生成列比生成表好用多了。
好了,现在我们来试试那个微软官方注释是否正确吧。结果真的让我莫名其妙!
看官们看出来了没有,面对不连续的数据,DATEADD没有报错啊,那个微软官方解释造谣?!可是我仔细看了下,更悲伤了,因为数据丢了!原来sheet1中提供了 7 条日期数据,但是新生成的表格中只有 5 条数据!why?!!!难道我一开始的理解有重大误区。你还别说,是的!
我重新试了很多种数据组合,搭配 DATEADD,大家瞪大眼睛看吧。第一种sheet1数据如下:
第二种sheet1数据如下:
总结:
1)日期列数据不连续不会报错!但是会对<日期列>重新排序
2)DATEADD 不是如我希望的那样把日期列数据依次推迟或推前,它是在现有数据中进行筛选,现有数据中如果有符合日期要求的留下来,不符合日期要求的剔除。怎么算符合日期要求呢?有一个逻辑:首先使用<日期列>中最早的那个日期,使用上<偏移量>, <偏移单位> 得到一个新的日期<新日期>。
比如sheet1第二部分数据中<日期列> 最早的日期是20240120,当<偏移量>, <偏移单位>是 2,DAY 时候,得到 <新日期> 即 20240122,那么原来的<日期列>中大于等于20240122的日期则形成了新表。
当<偏移量>, <偏移单位>是 1,QUARTER 时候,得到 <新日期> 即 202404,请注意是202404,即只要大于等于20240401即可,而不是一定要大于202420,那么原来的<日期列>中大于等于20240401的日期则形成了新表。
当<偏移量>, <偏移单位>是 1,MONTH 时候,得到 <新日期> 即 202402,那么原来的<日期列>中大于等于202404的日期则形成了新表。
说实话,我觉得这个用法好奇特!后来我想明白了,DATEADD 这个名字起错了,如果使用 DATEFILTER 或者 DATECHOOSE,感觉就会好多了。
2) DATEDIFF 函数:返回两个日期之间指定的间隔数。
语法:DATEDIFF ( <日期 1>, <日期 2>, <间隔单位> )
<日期 1>, <日期 2>, 都是日期格式的数据,<间隔单位> 则是有YEAR、MONTH、DAY、QUARTER、WEEK、HOUR、MINUTE、SECOND 8 个枚举变量。 我准备了一组数据,分别使用了这个8个枚举变量,结果如下:
总结一下详细用法:
a) 针对 YEAR、MONTH、DAY、QUARTER、WEEK、HOUR、MINUTE、SECOND 8 个枚举变量,DATEDIFF 比较的是其枚举量对应的值,如 YEAR,只比较年份,比如 20240528 和 20250109, 实际时间不到半年,但是DATEDIFF的结果是1。
b) <日期 1>早于 <日期 2>,则值为正值,相等为 0,反之为负值。
这个函数挽救了我被 DATEADD 伤害到的脆弱心灵。
3) DATESBETWEEN 函数
返回一个表,返回一个包含一列日期的表,这些日期以指定开始日期,一直持续到指定的结束日期。其实这就是一种筛选,这次名字起得比较好,比 DATEADD 强多了。
语法:DATESBETWEEN ( <日期列>, <起始日期>, <截止日期> )
给大家看一个用法:
4) DATESINPERIOD 函数
返回一个表,此表包含一列日期,日期以指定的开始日期开始,并按照指定的日期间隔一直持续到指定的数字。
DATESINPERIOD ( <日期列>, <起始日期>, <偏移量>, <间隔单位>
这个函数又一次伤到了我!它和DATEADD函数非常类似,也是筛选日期,区别在于它增加了一个<起始日期>,使逻辑更加复杂(或者说高效?)。但是在计算新日期时候两者有很大区别,DATEADD 是按照自然月、年等来计算,而 DATESINPERIOD 是按照周期月、年来计算。
总结一下用法:
a)如果<偏移量>是正值,
首先要在<日期列> 找出大于等于 <起始日期> 的所有值, 如没有则返回空表,结束。
接着在找到的值中寻找出最早的日期值<最早日期值>,然后根据<偏移量>, <间隔单位>得到一个新的日期值<最晚日期值>;
比如<偏移量>, <间隔单位>是2,DAY,从下面的数据中,筛选出<最早日期值>20240225和<最晚日期值> 20240226;
比如<偏移量>, <间隔单位>是1,MONTH,从下面的数据中,筛选出<最早日期值>20240225和<最晚日期值> 20240324;
最后使用<最早日期值>和 <最晚日期值>来筛选区间内的日期值。 b)如果<偏移量>是负值,
首先要在<日期列> 找出小于等于 <起始日期> 的所有值, 如没有则返回空表,结束。
接着在找到的值中寻找出最晚的日期值<最晚日期值>,然后根据<偏移量>, <间隔单位>得到一个新的日期值<最早日期值>;
比如<偏移量>, <间隔单位>是 -4,DAY,从下面的数据中,筛选出<最早日期值>20240328和<最晚日期值> 20240325;
比如<偏移量>, <间隔单位>是-1,MONTH,从下面的数据中,筛选出<最早日期值>20240228和<最晚日期值> 20240127;
最后使用<最早日期值>和 <最晚日期值>来筛选区间内的日期值。
5) DATESMTD 函数
返回一个表,此表包含当前上下文中该月份至今的一列日期。
语法:DATESMTD ( <日期列> )
还用下面语句生成的日期表
日期表 = CALENDAR( DATE( 2024 , 2, 25) ,
DATE( 2024 , 5 , 10 )
)
此时可以看出它是找出最晚的一个月的所有日期表。
6) DATESQTD 函数
函数返回一个表,此表包含当前上下文中该季度至今的一列日期。
语法:DATESQTD ( <日期列> ),这个从月换成季度,不再赘述
7) DATESYTD 函数
函数返回一个表,其中包含当前筛选上下文中当前年份至今的所有日期。
语法:DATESYTD ( <日期列> , [年截止日期] );
这个和DATESMTD、DATESQTD的区别在于新增了一个可选的参数[年截止日期] ;有了这个[年截止日期] 则不是默认筛选出来整年的日期,而是到了[年截止日期] 就可以了。
8) LASTDATE 函数
返回指定日期列在当前上下文中的最后一个非空日期。
语法:LASTDATE ( <日期列> )
还用该语句生成:日期表 = CALENDAR( DATE( 2024 , 2, 25) ,DATE( 2024 , 5 , 10 ) )
9) FIRSTDATE 函数
返回指定日期列在当前上下文中的最前一个非空日期。
语法:FIRSTDATE ( <日期列> )
还用该语句生成:日期表 = CALENDAR( DATE( 2024 , 2, 25) ,DATE( 2024 , 5 , 10 ) )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~·~~~~~~这篇博客写了 3 天,麻烦点个赞或者收藏再走吧!谢谢同学!~~~~~~~~~~~~~~~~