POWERBI｜时间智能函数

---

前言

在日常可视化中，时间一个不会缺少的维度。而在我们对数据进行展现的过程中，大部分情况下会跨越当前的时间颗粒度去展现数据，比如在月份上展现今年的销售总额，这个时候会需要使用到时间智能函数，下面就日期表建立、度量值书写以及keypoint三个方面来介绍时间智能函数。

---

一、创建日期表

1.创建日期表

制作日期表的方式有很多，这里不做详细介绍，创建日期表的方式最后不要用自动获取的方式（主要是模型里面可能会存在很多日期行，万一有出生日期这种，日期会从很早的日期开始）

日期表 = ADDCOLUMNS (
    
CALENDAR (DATE(2021,1,1), DATE(2022,12,31)),

"年度", YEAR ( [Date] ),

"月份", FORMAT ( [Date], "MM" ),

"年月", FORMAT ( [Date], "YYYY/MM" ),

"星期", WEEKDAY ( [Date] ) & "-" & FORMAT ( [Date], "ddd" ),

"季度", "Q" & FORMAT ( [Date], "Q" ),

"年份季度", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

展现出来的日期表如下：

2.标记为日期表

在制作完一张日期表后，并没有结束，我们需要把日期表标记为系统内的日期表，并明确日期列，并与事实表（如销售订单表）做关联，具体如下：

二、度量值制作

把常用的几个函数做以下分类，简单介绍几个时间智能函数的差别

1.累计型度量值

MTD&QTD&YTD

对于年度/季度/月度的累计，写法差不多都一直，以月度来举例。
一般我们都用datesmtd 或者totalmtd来实现对于月度累计的统计，这2种的差别在于datesmtd返回的是一张日期表，而total_mtd是一个计算表达式，写法上会有些差别。

mtd_datesmtd = 
calculate(
    sum('订单表'[net_gmv]),
    datesmtd('日期表'[Date])
    )

mtd_datesmtd = 
calculate(
    sum('订单表'[net_gmv]),
    datesmtd('日期表'[Date])
    )

季度以及年度的度量值就不枚举了，让我们来看下他们的数据展现：

让我们来解读一下结果，当高颗粒度度量值在低时间维度上展现时（比如季度值在月份值上做展现），高颗粒度度量值是截止到当前时间点的累计值。这个我们在后续的keypoint里会解释。

财年计算方式

关于财年累计，powerbi是提供了对于年度最后一天的设置，TOTALYTD跟DATESYTD函数均有可选属性。

ytd_datesytd_fi = 
calculate(
    sum('订单表'[net_gmv]),
    datesytd('日期表'[Date],"02/28")
    )

ytd_totalytd_fif = 
TOTALYTD(SUM('订单表'[net_gmv]),'日期表'[Date],all('日期表'),"02/28")

2月份的最后一天由于会变化，这里在闰年会出现小问题，展现如下：

2.时间平移型度量值

2.1.DATEADD&PARALLELPERIOD

lastmonth_dateadd = 
CALCULATE(SUM('订单表'[net_gmv]),DATEADD('日期表'[Date],-1,MONTH))

lastmonth_parallelperiod = 
CALCULATE(SUM('订单表'[net_gmv]),PARALLELPERIOD('日期表'[Date],-1,MONTH))

让我们来看下在不同时间颗粒度上的展示


让我们来解读一下这2个函数的差别，dateadd的用法是往前平移一定的月份，但是具体被平移的颗粒度是以展现的日期颗粒度来决定的，然后获取到最大的日期，往前平移一个月；而parallelperiod是不管你展现的是什么时间颗粒度，最终都以一定的颗粒度来展现，而这个颗粒度就是你书写在度量值里的颗粒度，而在本案例中它的运行机制相当于获取当前最大月份，往前平移1个月。

2.2.DATEADD&SAMEPERIODLASTYEAR&DATRSINPERIOD

lastyear_dateadd = 
CALCULATE(SUM('订单表'[net_gmv]),DATEADD('日期表'[Date],-1,YEAR))

sameperiodlastyear = 
CALCULATE(SUM('订单表'[net_gmv]),SAMEPERIODLASTYEAR('日期表'[Date]))

datesinperiod = 
CALCULATE(SUM('订单表'[net_gmv]),datesinperiod('日期表'[Date],LASTDATE('日期表'[Date]),-1,YEAR))

展现如下

解读一下，使用dateadd 跟sameperiodlastyear基本上是保持了一致，而datesinperiod则是在某一时间上的近一年的累计数值。

2.3.上月同比

在实际的业务过程中，发现在月度数据展现时，经常会出现一种比较，在月度已经是完整月时，我们希望对比完整月份与完整月份的对比，而在当下月份时，希望比对同一时期的数据，可以在时间智能函数上做一些加工。

lastmonth_con_gmv = 
CALCULATE(SUM('订单表'[net_gmv]),
DATEADD('日期表'[Date],-1,MONTH),
'订单表'[date_id]<date(year(TODAY()),month(today())-1,day(today())
))

看下结果

可以看到在最后一个月份得到的上月gmv并不是完整月份的，而是基于我们限制的日期求出来的gmv。

三、KEYPOINT

1.为什么要建立日期表，并且一定需要标记成日期表？

• 标记成日期表的原因可能大家都很好理解，因为我们在事实表中一般都没有连续的日期，而很多日期函数都是建立在连续的日期的概念上，所以需要建立日期表，并且在视图使用日期表中的字段进行展示。
• 标记成日期表是由于在模型中的表间关联键是非日期格式时，如果不标记成日期表，那么不能覆盖日期表其他列的筛选条件。比如日期表的日期列为date字段，但关联键为datekey(yyyymmdd)整型类型，那么不标记成日期表时，时间智能函数将无法覆盖其他列的筛选条件。也可以理解为，当标记成日期表时，时间智能函数会在后面加上all(‘日期表’)的条件。

2.为什么QTD在月份上展现时，是一个累加过程？

• 这个问题需要让我们先了解下时间智能函数的大致运行逻辑，首先我们试着不使用时间智能函数去做一个mtd

mtd_normal = 
var max_year = MAX('日期表'[年度])
var max_month = MAX('日期表'[年月])
var max_date = MAX('日期表'[Date])
return
CALCULATE(SUM('订单表'[net_gmv]),
        FILTER(all('日期表'),
            '日期表'[年度]=max_year
            &&'日期表'[年月]=max_month
            &&'日期表'[Date]<=max_date))

看一下结果

解读一下，其实大部分时间智能函数也都是先获取一个最大的日期，比如qtd在1月份上展现时，先获取到了最大的日期是1月31日，那么在数值计算时，则计算了从季度初到最大日期的总和。