4.3 功能强大的时间智能函数

本节简介

本文中我们来认识DAX中非常重要的一组函数，即时间智能函数。日期是各类报表最基础的统计维度，通过使用时间智能函数，可以很简易地完成常用的同/环比、累计等统计计算。

此外，在前文中我们曾介绍，calculate函数能接受的筛选器有三种，当时由于还没有开始学习其余的函数，因此只演示了布尔值列表一种。而时间智能函数的学习过程中，正好可以了解用表来充当筛选器的应用。

构建日期表

在之前讲模型的时候，我们就提到过，日期维度通常都应该单独创建维度表。不光是为了能同时使用一张维度表来筛选整个模型，也是因为许多时间智能函数要求底表中具备连续的日期，否则会报错。

用CALENDAR函数构建日历表

实际操作中，由于报表中的数据是不断增加的，专门在外部创建一个日期表不太有必要。我比较推荐的是使用CALENDAR函数来构建，该函数只有两个参数，即开始日期和结束日期，然后会返回一张包含这两个日期之间全部日期的表。

如下图所示，在数据视图中点击“表工具”，会激活公式输入窗口，输入

日期表示例 = CALENDAR(date(2023,3,1),date(2023,3,10))

在公式中，我们使用date函数构建了两个日期，于是得到了3月1日至10日之间的日期表，每一天都有一行记录，连续无间断。

但是这样手动输入起止日期，似乎也不满足自动更新的需求。这时一种做法是直接创建一个较大的日期范围，另一种就是动态地获取模型中的日期区间范围，并生成日期维度表。

那么看一下下面这个公式：

日期表 = CALENDAR(FIRSTDATE('数据表'[日期]),LASTDATE('数据表'[日期]))

这个公式中，我们使用了firstdate和lastdate两个时间智能函数，顾名思义，它们都是接受一个日期列，然后分别返回当前上下文中的第一个/最后一个日期。

当报表刷新时，随着数据表中的日期增多，我们的日期表也会同步增加。在实际操作时，要注意一点，就是用于提供日期区间的字段一定要覆盖模型的分析区间，所以应该选择数据最全的字段作为时间智能函数的参数，而且也不必拘泥于同一张表，起止日期可以来自不同的表或字段。

以财务分析为例，在一年没有过完之前，实际数据中可能没有未来的日期，而预算数据虽然会有全年的日期，但是通常只会保留当年的数据，与实际数相比会缺少历史年份的日期。那么完全可以使用实际数来提供firstdate，用预算数提供lastdate。

添加辅助维度

维度表还有一个优点就在于，可以提供更多丰富的附加维度，然后通过关系模型完成对数据的筛选。所以我们在创建了日期表之后，可以根据分析需求为其添加一些附加的维度列。添加新列的方法是，在选中当前日期表之后，点击菜单栏的“表工具”或“列工具”中的“新建列”，然后在公式输入框中输入对应的公式。

下表是我们添加了一些常见的辅助维度后的日期表：

以下简单介绍一下这些列对应用到的公式：

年份 = year([Date])

月份 = month([Date])

星期 = weekday([date],2)

周数 = WEEKNUM([Date],2)

季度 = QUARTER([Date])

月份英文 = FORMAT([Date], "MMMM")

月份简写 = FORMAT([Date], "MMM")

数字月份 = FORMAT([Date],"m月")

月份补零 = FORMAT(month([Date]), "00")

其中年、月、星期、周数、季度等很简单，跟excel中没什么区别，其中weekday，weeknum的第二参数用于选择每周从周几开始，不需专门记忆，用的时候试一下就行了。

到了月份的英文名称开始，可以看到我们新引入了一个format函数，这个函数类似excel中的text函数，用于将第一参数给定的列转换为第二参数指定的格式，这个格式同样是要用英文双引号，这里我们不多讲了，未来在应用到的时候再说。

常用时间智能函数

接下来我们先了解一下要使用的示例数据，然后进入函数的学习。

我们练习时使用的模型包括两张表，日期表就是刚才使用CALENDAR函数创建的动态日期维度表，它通过 日期列关联到“数据表”。

数据表中：日期列包含20230101-20230310之间的日期，为了观察起来方便，时点指标列中的值为月份数*100+当天的天份，例如1月1日是101，1月31日是131，而期间指标列中每一行均为1。

FIRSTDATE & LASTDATE

这两个函数都只有一个参数，我们需要提供给函数一个日期列，然后会返回在当前上下文中的第一个/最后一个日期。除了在创建日期表时之外，我们也经常会遇到需要使用lastdate来辅助获取最后一天指标值的场景。

例如下图的矩阵中，我们将日期表的月份和日期放在了行中，值中放的是时点指标。所谓时点指标都是用来体现统计时点的规模的，例如用户量、库存量等，因此它们在不同日期之间是不能求和的。

然而在下图中可以看到，可以看到单天的值是准确的，但在月份以及总计的汇总行处，由于程序仍然是用默认的求和操作，就出现了一个巨大的毫无意义的值。

那么怎么办呢？此时我们新建以下一个度量值并放入矩阵中：

时点lastdate = CALCULATE(sum('数据表'[时点指标]),lastdate('日期表'[日期]))

由图中可以看到，这次就实现了我们想要的统计效果。

对1月合计来说，当前上下文中有当月的全部日期，因此lastdate提供了2023年1月31日给calculate函数，因此获取到了31日的时点指标，计算得到131。2月同理，最大日期是28所以结果是228。在整个表的总计中，因为上下文中是汇总了整个日期表，它得到的lastdate是3月9日，因此度量值的计算结果显示为309。

而在下钻到日期列时，对每行来说的上下文就是对应的日期，所以显示的就是每天的值。

这部分需要理解的就是，度量值在视觉对象中的每一个点位都是结合对应的上下文进行计算的，只有深入理解上下文才有助于写出正确的度量值。

除了像这样在对日期进行穿透下钻的场景外，当我们使用日期区间形式的切片器、或是切片器中不指定具体时间想实现随着数据更新取最大日期的时候，都可以利用lastdate函数来实现。

DATESYTD 、DATESQTD 、DATESMTD

这一组函数的输入也是只有一个日期列，但它们返回的不是一个日期值，而是一系列日期值组成的表。

DATESYTD返回的是当年第一天到当前日期的所有日期；DATESQTD和DATESMTD同理，分别是当季和当月的第一天到当前日期的每一天。注意这里的当前日期来自当前上下文中的最大日期。

这些函数最常见的使用场景用于统计期间类指标，即体现业务在统计期内的发生额，如销量、产量、收入等。

如下图所示，仍然是刚才的矩阵，我们将期间指标直接放在矩阵中，对期间指标来说，可以看出在各个月份的合计中准确显示了当月的全部业务量，单天的值是1所以1月合计31、2月28，3月数据只有9天所以是9。

右侧是使用datesmtd和datesytd统计的结果，果然不论是在月份汇总行、全年总计行、以及日期明细行都实现了正确的累计效果。

对应公式如下：

期间mtd = calculate(sum('数据表'[期间指标]),DATESMTD('日期表'[日期]))

期间ytd = calculate(sum('数据表'[期间指标]),DATESYTD('日期表'[日期]))

我们以MTD版公式来简单解析一下，对于表中的每一行，先确定对应的当前日期，像1、2月日期是完整的，所以当前日期是月末值，3月只到9日所以是3月9日。然后返回了从对应月份的1号开始到当前日期的所有日期行。于是calculate按照这些日期行对数据表进行筛选汇总，求得了各行的MTD值。

DATEADD

函数dateadd有三个参数，写法是这样的： dateadd(日期列,时间间隔,间隔单位)

其作用是将当前上下文按照指定的时间间隔推移，返回对应的区间。时间间隔可以是正数或负数，正数表示向后负数为向前，间隔的单位可以是day,month,year,quarter。

利用dateadd可以很方便地构造各种相对日期，实现同/环比的计算。

譬如我们新建一个度量值：

时点上月 = CALCULATE(sum('数据表'[时点指标]),LASTDATE(DATEADD('日期表'[日期],-1,MONTH))

该度量值先确定当前的上下文，然后将上下文中的所有日期都减一个月，再按照lastdate在dateadd返回的区间中取最后一天，传给calculate函数用于计算。

于是我们看到如上图所示，对1月来说由于缺失2022年12月数据，因此度量值的统计结果为空。2月汇总行中，对2月整体向前推一个月是1月，最后一天是1月31日，所以统计得到131。3月同理，虽然日期表中只有9天，但在汇总时还是返回了2月的最后一天即228。总计行也是一样，得到2月底的值。

而在日期明细行中，就是分别获取到了上月对应天的指标值。

小结

本节中我们学习了使用calendar函数创建日期维度表的方法，并学习了最常用的三组时间智能函数。日常只要熟练掌握它们，通过各种组合嵌套就足够应对绝大部分统计场景了。

其中在YTD,QTD,MTD的统计中，我们给到calculate的筛选器就是一张表，这也是用表来做筛选的最常用的方法。