33个时间智能函数 | PowerBI星球

时间可以说是数据分析中最常用的独立变量,工作中也常常会遇到对时间数据的对比分析。假设要计算上年同期的销量,在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([数量],DATESYPD(日期表[日期],"9-30"))

等同于

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

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

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

时间智能函数与日期表

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

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

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

  • 起止日期涵盖事实表的所有日期

  • 日期是连续且不重复的

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

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

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

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

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

(0)

相关推荐

  • date_sub函数

    作用:从某日期减去指定的时间间隔后的日期 用法:date_sub(date,  interval expr type); date 参数是合法的日期表达式.expr 参数是希望添加的时间间隔. sel ...

  • 用PowerBI制作上市公司财务报表分析模型

    我花了两周时间对采悟老师的上市公司财务报表分析模型进行了像素级的复刻,这个过程中学到了非常多的PowerBI建模及可视化设计的技巧,受益良多. PowerBI星球财务分析模型: 我将分几篇文章,在这里 ...

  • 财务人员经常与时间打交道,这么简单的时间函数怎么能不掌握呢?

    时间对于财务工作很重要,因为会计处理是需要分期的! 时间对于财务工作很重要,因为很多时间点是法定的! 所以,我们很多财务工作中需要"时间"! 在Excel中"时间&quo ...

  • DAX Patterns | 关于时间的标准计算

    关于这个模式 在这个模式中,我们将向你展示如何使用标准日历计算与时间相关的计算,如年初至今.去年同期和百分比增长.使用标准日历的最大优点是:你可以依赖多个内置的时间智能函数.内置函数的设计方式是为最常 ...

  • FILTER函数 | PowerBI星球

    介绍过CALCULATE函数之后,有必要再介绍它的最佳搭档:FILTER函数. CALCULATE函数的第二个及之后的参数是筛选条件,而FILTER函数正是为筛选而生. FILTER语法 FILTER ...

  • EARLIER 函数 | PowerBI星球

    前面利用PowerBI做数据分析的时候都是对整列的字段进行操作,并没有做更细化的分析,比如分析数据的每一行.提取某一行的数据,这在Excel中很容易实现,因为Excel公式是对单元格操作.那么在Pow ...

  • 2月29日就到要了,Power BI时间智能函数是如何处理闰年的?

    今年是闰年,马上就到2月29日了,有星友问我,在Power BI中,2月29日的上年同期是怎么计算的? 这是个好问题,正好梳理一下,PowerBI时间智能函数是如何处理不规则日期的对比数据的. 以一个 ...

  • 度量值 | PowerBI星球

    本文学习PowerBI最重要的概念:度量值 初学Power BI一般都会对度量值比较困惑,毕竟对长期接触Excel的人来说,这是个全新的概念,但一旦理解了她,肯定会立即喜欢上. 简单来说,度量值是用D ...

  • DAX是什么? | PowerBI星球

    DAX是英文Data Analysis Expression的缩写,意思是数据分析表达式,从名称上就可以看出,DAX公式是用作数据分析的,事实上也确实如此,从数据分析层面认识DAX会更有助于我们理解它 ...

  • 理解CALCULATE | PowerBI星球

    介绍DAX的时候,特别强调过一个重要的函数:CALCULATE,本文就来揭秘这个函数的计算原理以及它是如何影响上下文的. CALCULATE的语法结构 语法: CALCULATE(<expres ...

  • 普通到没颜值的柱形图?| PowerBI星球

    前面通过大量时间学习那些可能会让你感觉枯燥的数据处理.数据建模等内容,到这里我们开始涉足PowerBI中最让人心动的环节:数据可视化. 第一篇先在PowerBI中制作常见的柱形图,简单来说,柱形图就是 ...

  • 折线图 | PowerBI星球

    如果要展现数据的趋势变化,折线图应该是不二之选,并且它更擅长于展现时间序列下的数据,根据折线斜率的不同展现变化的速率. 同柱形图一样,折线图同样很大众化,每个人都见过,这本身也成了它的一个优势,就是非 ...

  • 面积图 | PowerBI星球

    面积图主要是反映各类别数据变化的趋势以及占比情况.在PowerBI默认的可视化对象中,有两种面积图:分区图和堆积面积图. 分区图 分区图是一种标准的面积图,下面用分区图来展现每天各种渠道的点击量变化趋 ...