数据可视化之DAX篇(十二)掌握时间智能函数,同比环比各种比,轻松搞定!

https://zhuanlan.zhihu.com/p/55841964

时间可以说是数据分析中最常用的独立变量,工作中也常常会遇到对时间数据的对比分析。假设要计算上年同期的销量,在PowerBI中可以用CALCULATE来写个度量值[上年同期],

= CALCULATE([数量],
SAMEPERIODLASTYEAR('日期表'[日期]))

 

这里SAMEPERIODLASTYEAR就是时间智能函数。

 

时间智能函数和普通函数的区别

 

时间智能函数与普通的时间或者日期函数的区别是,

  • 日期函数直接依赖当前行上下文,一般作为新建列使用,比如YEAR函数,提取日期列的年度;
  • 时间智能函数会重置上下文,一般新建度量值时使用,可以快速移动到指定区间

 

时间智能函数有哪些

在PowerBI中,目前共有33个时间智能函数,列表如下:

 

 

看起来好像很多,很难记,其实都是用很直白的英文表达,函数名的意思就是这个函数的功能,有些函数都包括几个粒度,比如PREVIOUSYEAR/QUARTER/MONTH/DAY,上一年/季/月/天,还有相反方向的下一年/季/月/天,NEXTYEAR/QUARTER/MONTH/DAY,这8个函数可以看作是一类,上表中排序也是把同一类的函数按顺序放到了一起,这样看下来并没有几种。

根据这些时间智能函数计算的结果,我把时间智能函数分为以下两类:

  • 返回期间的时间智能函数

返回期间并执行运算的时间智能函数

返回期间的时间智能函数

 

前24个函数都属于这种,其中前20个时间智能函数都是非常简单的,只需要使用一个日期参数就可以,返回对应的时间期间,一般结合CALCULATE使用,比如本文开头的SAMEPERIODLASTYEAR看起来很长,其实非常简单,从函数名称可以看出它是干什么的:上年同期。

第21至24,DATEADD、DATEBETWEEN、DATESINPERIOD 、PARALLELPERIOD ,这4个函数相对复杂一点,但也就是参数多了两个而已,一般用于相对时间区间的控制,请看下图中对这4个函数的介绍。

 

 

 

 

返回期间并执行运算的时间智能函数

 

从第25个开始,最下面这9个函数更加智能,不仅可以重置上下文,甚至直接对重置后的下上文执行运算,把CALCULATE都省掉了,比如求年初至今的销量,使用TOTALYTD函数:

=TOTALYTD([数量],日期表[日期])

它和之前使用DATESYTD的效果等同

=CALCULATE([数量],DATESYTD(日期表[日期]))

 

OPENINGBALANCEYEAR,返回当前行上下文对应的年初余额,如果这个销售记录表上还有一列当日结束时的[库存量],新建度量值

=OPENINGBALANCEYEAR(销售记录表[库存量],
日期表[日期])


将返回2017年的年初库存,也就是2016年12月31日结束时的库存量。

CLOSINGBALANCEYEAR/QUARTER/MONTH计算年/季/月末的数据,参数和使用方法类似。

 

涉及年度的时间智能函数的特殊用法

 

在33个时间智能函数中,有8个是关于年度的,

DATESYTD 
PREVIOUSYEAR 
NEXTYEAR 
ENDOFYEAR 
STARTOFYEAR 
TOTALYTD 
CLOSINGBALANCEYEAR 
OPENINGBALANCEYEAR

这8个函数的参数中最后都有一个可选参数<year_end_date>,意思是年度结束日期,默认情况下就是自然年度的结束日期12月31日,可以省略,在特定的分析中,年度结束日期不是12月31日,那么可以使用这个参数来界定。

在进行财年的分析时,很多国外的公司的财年都不是自然年度,比如苹果的财年结束日期9月30日,2017财年就是从2016年10月1日到2017年9月30日,计算2018财年的年初至今的销量:

=CALCULATE([数量],DATESYTD(日期表[日期],"9-30"))

等同于

TOTALYTD=([数量],日期表[日期],"9-30")

都将返回从2017年10月1日到目前的累计销量。

其他几个函数不再一一介绍。

 

时间智能函数与日期表

 

使用时间智能函数时若出现一些莫名其妙的错误,或者返回的数据难以解释,很大可能是你使用的时间参数有问题。

为了科学的使用时间智能函数,首要的就是建立一个合格的日期表,并将日期列与事实表的日期列建立关联。

合格的日期表具备以下的特征:

  • 起止日期涵盖事实表的所有日期
  • 日期是连续且不重复的

如何生成日期表,有很多方法,可以用Excel、M函数也可以用DAX生成,这里就不细说了,日期表导入到PowerBI Desktop后,首先要把它标记为日期表,

 

 

因为日期表的起止日期我们很容易查看是否涵盖,但是否连续不重复却并不能轻易看到,标记为日期表的好处是,它会强制检查日期表的日期列是否为日期格式、是否为连续的、不重复的,如果不是,则无法进行标记,这样的强制要求保证了后面使用时间智能函数时,不会出现这方面的错误。

日期表并不一定要按照网上搜罗的方法生成同样的粒度列,而是要根据实际分析的需要,建立适合的相关列,比如要按财年来分析,就要按照财年的起止日期来添加财年列;如果要进行最近12个月的滚动分析,就添加一个月份编号。

日期表包含分析需要的维度列,可以简化DAX的嵌套,建立简单的度量值就可以轻松聚合所需要的数据。这也是合格的日期表和优秀的日期表的分界线。

时间智能函数并没有什么神秘,熟悉它使用的参数、搞清它重置的日期区间和返回的数据类型,就掌握了它,在实际分析中多加练习就可以灵活的使用这些函数。选取特定的时间粒度,快速筛选统计区间,轻松搞定时间序列分析。

 

posted @ 2020-05-01 19:01  秋华  阅读(3443)  评论(0编辑  收藏  举报