【BI比较】DAX时间智能函数，与SQL/Tableau对比

在学习Power BI和DAX的过程中，大多数人都会被其中智能的“时间智能函数”所吸引，这也是它在财务领域广受好评的原因之一。日期智能函数可以视为时间函数和条件函数的组合，DAX产品经理把二者组合封装为一系列的函数，降低了引用的难度。

本文中，喜乐君将参考PBI中时间智能函数的基本结构，介绍在SQL和Tableau的实现方式，并对二者的逻辑做对比介绍。

一、时间智能函数相关概念与分类

在DAX大神Alberto Ferrari和Marco Russo的DAX Patterns一书中，开篇介绍了时间智能函数的使用，书中使用了如下的命名约定，本文也将遵循此约定。比如YTD代表年初至今，PY代表上一年。同时，作者把它们分为三个类型：

• Shift偏移：相当于当前/观察日，沿着特定方向偏移观察周期，比如py上年
• Aggregation聚合：在观察范围基础上，对特定度量值的聚合计算，比如ytd累计
• Comparison比较：在聚合基础上，对两个观察周期的聚合值执行比较计算，比如yoy同比

读者可以把上述三种类型视为“前后相继”的组合形式，累计计算必然基于偏移的观察周期，而比较则又基于聚合。

接下来，本文以YTD、PYC上年、PY上年为例，介绍SQL和Tableau中的实现逻辑，而后介绍DAX时间智能函数与之的不同。

二、入门：YTD期初至今范围和TOTALYTD累计

本小节，以DATESYTD、PREVIOUSYEAR、TOTALYTD为例，介绍DATES*开头的日期范围函数、PREVIOUS*开头的和TOTAL&开头的日期范围累计函数。这里的观察日都是当前（today）。

1、年初至今的日期范围

DAX中年初至今累计可以用DATESYTD函数完成，如下所示：

    MEASURE Sales[Sales YTD] =
        CALCULATE (
            [Sales Amount],
            DATESYTD ( 'Date'[Date] )
        )

在这里，DATESYTD返回的是日期表Date中从年初到today的日期范围，依次作为筛选条件，计算聚合值[Sales Amount]。

在SQL、Tableau甚至Excel中，这个“日期智能函数”可以用普通的日期函数完成，如下：

Period_Datesytd = 
IF 
DATEDIFF('year', [order_date], today() ) =0  // 假设没有今日之后的订单日期
THEN [date]
END 

喜乐君倾向于使用DATEDIFF范围计算一次性完成范围判断；上述范围判断的部分也可以用用如下方式替代：

YEAR([order_date]) = YEAR (today())  // 假设没有在今天之后的订单日期

假设数据表中存在今日之后的数据（比如目标表），那么还需要增加一个判断，如下所示：

DATEDIFF('year', [order_date], today() ) =0 AND [order_date] <= today() 

YEAR([order_date]) = YEAR (today()) 
AND
[order_date] <= today() // 假设存在今天之后的订单日期，增加判断保留今日之前 

本质上，这里是把DAX 时间智能函数转换为了普通的日期和时间函数，相当于反向的解封过程；当然二者的逻辑差异巨大，稍后介绍。

2、年初至今日期范围基础上的度量累计

在上述基础上，这里增加一个销售额累计计算。在DAX中，常见两种方式完成，其中第二种方法是第一种的简化，如下：

MEASURE Sales[Sales YTD] =    // 方法1 ，使用datesytd
     CALCULATE (
         [Sales Amount],
         DATESYTD ( 'Date'[Date] )
     )

MEASURE Sales[Sales YTD] =    // 方法2 ，把datesytd   +聚合函数之间封装为 totalytd
    TOTALYTD ( 
            [Sales Amount],
            'Date'[Date]  

在SQL、Tableau中，可以把上一小节的日期判断和聚合结合起来，SQL中常见的方式如下：

SELECT 
SUM( Profit) , 
SUM( if( year(`Order Date`)=YEAR ( CURDATE() )  , Profit, 0) )  as YTD利润总和   -- SUM +IF
FROM tableau.superstore

Tableau中的原理也是如此。比如，喜乐君常常会写如下的计算：

YTD销售额  =   // 假设所有日期都在today之前
SUM(IIF( DATEDIFF('year', [order_date], today() ) =0 , [sales],null  )  

⚠️ SQL中的datediff函数没有year/quarter/month的间隔符，所以这里直接使用year函数，避免范围判断。Tableau中的datediff可以设置差异计算的日期部分，更加灵活。

3、同期范围及同期比较

借用上述的方法，我们就可以轻松完成PYC上年的销售额、PY上年同期销售额、yoy同比百分比的计算。其中设计的是PREVIOUS*开头的日期范围函数（第四部分会详细介绍）

PYC上一年完整的日期范围，可以用一个DATEDIFF完成，性能上胜过YERA的组合：

IIF( DATEDIFF('year', [order_date], today() ) = 1 // 和今天相比差异年度为1，即上一年

PY上一年同期的范围，则仅需要在PYC的基础上增加一个范围范围，两个范围计算交集即可：

IIF( DATEDIFF('year', [order_date], today() ) = 1 // 和今天相比差异年度为1，即上一年
AND
[order_date] <=  today()-365 

在这里，喜乐君特别推荐 TODAY() -365 的写法，替代dateadd函数，既简单又易于理解。涉及到天级别的计算，直接使用算术计算，是最简单的方式，这个原则适用于Tableau，也适用于DAX。

基于PY和PYC的计算，就可以在结合聚合实现去年同期销售、去年销售的计算了。对于Tableau而言，喜乐君常常使用的方式计算“PYC上年销售额”：

PYC上年销售额  =     // 假设所有日期都在today之前 
SUM(IIF( DATEDIFF('year', [order_date], today() ) =1 , [sales],null  )   

只需要增加一个范围判断，就可以转换为“PY上年同期销售额”，如下：

PY上年同期销售额  =     // 假设所有日期都在today之前 
SUM(IIF( DATEDIFF('year', [order_date], today() ) =1  AND [order_date] <= today()-365 , 
        [sales],null  )   

对于SQL来说，逻辑也是完全相同的，如下展现了常见的计算。

SELECT  
SUM( Profit)  as 所有年度利润总和,  
SUM( if( year(`Order Date`)=YEAR ( CURDATE() )  , Profit, 0) )  as YTD利润总和   -- SUM +IF 
SUM( if( year(`Order Date`)=YEAR ( CURDATE() )-1  , Profit, 0) )  as 上年利润总和   -- SUM +IF 
SUM( if( year(`Order Date`)=YEAR ( CURDATE() )-1  and  `Order Date` < CURDATE()-365, Profit, 0) )  as 上年同期的利润总和   -- SUM +IF 

FROM tableau.superstore

沿着类似的方式，我们可以实现 QYT、MTD、PQ、PM等的累计和范围计算。

更多的内容，可以参考： Tableau计算MTD/YTD、去年同期MTD/YTD的方法

FUNCTION	DESCRIPTION描述
DATESMTD	Returns a set of dates in the month up to the last date visible in the filter context. 从月初到当前日期的范围（对应一个日期表）
DATESQTD	Returns a set of dates in the quarter up to the last date visible in the filter context.
DATESYTD	Returns a set of dates in the year up to the last date visible in the filter context.
NEXTDAY	Returns a next day.
NEXTMONTH	Returns a next month.
NEXTQUARTER	Returns a next quarter.
NEXTYEAR	Returns a next year.
PREVIOUSDAY	Returns a previous day. 前一天，可以直接用 date-1完成
PREVIOUSMONTH	Returns a previous month. 上一个月，可以用 MONTH([date]) -1理解
PREVIOUSQUARTER	Returns a previous quarter.上一季度
PREVIOUSYEAR	Returns a previous year. 上一年度，简称 PY
STARTOFMONTH	Returns the start of month.
STARTOFQUARTER	Returns the start of quarter.
TOTALMTD	Evaluates the specified expression over the interval which begins on the first of the month and ends with the last date in the specified date column after applying specified filters.
TOTALQTD	Evaluates the specified expression over the interval which begins on the first day of the quarter and ends with the last date in the specified date column after applying specified filters.
TOTALYTD	Evaluates the specified expression over the interval which begins on the first day of the year and ends with the last date in the specified date column after applying specified filters.

三、时间智能函数的独特性和难点

1、使用时间智能函数有一个级别前提：需要自动或手动创建日期表。

换句话说，时间智能函数是基于数据表模型的，基于事实表和日期表之间的匹配关系，而非基于单表逻辑。这一点，犹如高高的围墙，瞬间挡住了很多人使用时间智能函数的热情。日期表有几个关键要求（参考我sqlbi）：

• 日期表中应该覆盖所有的日期。 All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range.
• 需要有一个唯一的日期/日期时间格式字段列，并且数据值唯一。There needs to be a column with a DateTime or Date data type containing unique values
• 在模型中标记为“日期表”，从而构建日期模型关系。The Date table must be marked as a date table in the model,

可见，日期智能函数的超级便利，是建立在更抽象、更高级的数据表模型基础上的，正所谓，产品经理为你打开了一扇窗，但是需要先爬上三层楼。

2、DATEYTD返回的是数据表，而非布尔值

在计算YTD时，可以使用DATEYTD来计算年初至今的范围，特别注意的是，这个范围的结果从日期表中返回的数据表。如同FILTER的数据表一样：

// DATESYTD ( 'Date'[Date] )  的结果是一个数据表 
FILTER( 
   "date",
   YEAR([date]) =2023 
  ) 

而在普通的日期函数计算中，YEAR([date]) =2023返回的是布尔值，是在每一行中返回的判断。这也是SQL、Tableau中采用的逻辑。

只有深刻地理解了数据表在时间智能函数中的作用，结合数据表模型的理解，DAX的应用才能算是游刃有余。

3、时间智能函数，和迭代的关系

使用时间智能函数，分析师可以在视图中，为每一行单独计算范围和累计计算，这是DAX单一计算相比SQL、Tableau的超群之处，只此一点，似乎前面的困难都有了必要性。类似的结果，在sql/Tableau中要用窗口函数完成。

首先，我们使用calculate或者totalytd完成年初至今累计计算

MEASURE Sales[Sales YTD] =    // 方法1 ，使用datesytd
     CALCULATE (
         [Sales Amount],
         DATESYTD ( 'Date'[Date] )
     ) 

之后，我们把这个计算放在年月为分类维度的视图中，每一行都会自动把自己作为“观察日”，计算观察日对应的年初到当前的累计，结果如下图所示，

作为对比，我们无法基于 YTD销售额 = SUM(IIF( DATEDIFF(‘year’, [order_date], today() ) =0 , [sales],null ) 这样的计算，完成类似的结果，上述结果只能适用于单一观察日，而无法适用于一个视图中的多个观察日。

用DAX的话来说，上述的计算返回的是单一个标量值（scalar ），而非一个数据表。

当然，如果想要在SQL和Tableau中实现相同的逻辑，我们只需要使用窗口函数即可，窗口函数可以实现指定范围的计算：

Tableau窗口函数的优势是“所见即所得”，聚合后的二次计算更容易理解，这一点类似于excel的单元格计算。

而在SQL中，也可以使用代码实现完全相同的逻辑，它用表计算的partition by控制年度区间，确保每年重新计算YTD，并使用order by控制次序，确保每一次计算都能到当前即止。如下所示：

个人之间，DAX的方式非常适合于高级的开发者，但是对于普通的业务分析师而言，它需要极强的耐心和极好的学习力方可驾驭。

四、日期范围偏移函数：DAX和Tableau实现逻辑对比

在DAX中，除了DATES* 、Total*三个类型的函数之外，还有以PREVIOUS*、NEXT*偏移函数，和STARTOF*、ENDSOF*期初/期末函数。

• PREVIOUSDAY/MONTH/QUARTER/YEAR 前一天/月/季度/年
• NEXTDAY/MONTH/QUARTER/YEAR 后一天/月/季度/年
• STARTOFMONTH/QUARTER/YEAR 月初/季度初/年初
• ENDOFMONTH/QUARTER/YEAR 月末/季度末/年末

1、Previous*往前偏移的日期智能函数

PREVIOUSDAY ([date] ) 函数用于范围日期对应的前一天的范围，注意返回的结果依然是数据表。

比如，dates是一个数据表，那么PreviousDate函数就会返回视图每个日期的前一天数据。使用PreviousMonth可以返回上一个月的日期范围，在与聚合函数结合，就能完成PM上月的聚合值，对应的聚合值逻辑如下：

Calculate ( 
    SUM(sales) , 
     PreviousMonth ('date'[date] )
  ) 

可以把上述的逻辑整合在一起，如下图所示。虽然视图中分类维度是“日期”，但是calculate表达式依然完成了对应上个月的完整聚合值。 如下所示：

在SQL和Tableau中，如果观察点是固定的——最常见的是相对于today观察“之前”，那么可以使用SUM( IF [order_date] = Today() -1 THEN [sales] end )的方式返回previousday的sales并聚合。

• 昨日 ：SUM( IF [order_date] = Today() -1 THEN [sales] end )
• 上月：SUM( IF datediff(‘month’, [order_date] ), Today() )=1 THEN [sales] end ) // tableau逻辑
• 去年：SUM( IF datediff(‘year’, [order_date] ), Today() )=1 THEN [sales] end ) // tableau逻辑

当时，如果想要实现上图中DAX的便宜计算——视图中存在日期，偏移是相对于每一行的日期、而非唯一的观察日计算范围，那么有需要回到窗口计算完成。

在Tableau中，不管是MTD的累计，还是指定月份范围的合计，都是表计算（也可以称之为窗口计算），在这里，类似于datesmtd的日期范围，被SQL中的 over函数所指定——只是SQL是绝对的指定字段，Tableau可以相对的指定范围（表/区/单元格的范围指定，和向下/向右的方向指定）。

某种意义上，Dax的表模型、表范围逻辑，和Tableau的可视化、窗口范围逻辑，都并非业务用户所能直接理解的范畴，都建立在抽象的数据表之上，因此是业务分析师迈向高级分析需要跨过的关键门槛。

总结一句关键的话，只要涉及到排序、移动平均、累计计算、循环等相关的计算，都要优先考虑窗口函数（SQL window calculation或Tableau table calculation表计算）。

2、NEXT*偏移函数

Next偏移方向和previous正好相反，对于SQL和Tableau而言，并没有单独的两套函数，而是使用asc/desc或者 +1/-1的参数方式来指定前后。

从这个角度看，DAX为了业务更好地理解，把参数调整改为函数语言，简化了理解复杂性，同时也增加了函数数量，有得有失。

这里不再展开，读者可以参考前面previous自行理解。

3、Startofmonth和endofmonth期初、期末函数

包含of字样的函数，是从日期范围中查找期初、期末的日期值，这在财务、仓储等领域中应用广泛。

比如说，当前是May 28, 2023 ，那么对应的期初就是May 1, 2023，而期末则是May 31, 2023.

如下图所示，在一个日期表中，我们可以用startof和endof函数直接获得不同口径的期初、期末值。

在业务中，Startof*和endof*函数更重要的用法，是结合calculate函数获得期初、期末的聚合值——如同totalytd，如下所示，增加一个月初的数量和月末的数量：

在财务分析中，常常出现“月初和月末的平均值”这样的计算，就会用到这样的计算；当然对于复杂的组合，实践中还需要VAR的变量和DEFINE的定义，方可实现语法上的简洁。

说到这里，SQL和Tableau中的逻辑还是只能依赖于表计算，和Previous一样，这里可以使用Lookup函数结合first/last更精准地查找期初、期末的数据值。相比代码化的DAX，喜乐君认为Tableau和SQL的窗口计算的方式，更容易理解。

最后总结一下，DAX的时间智能函数是建立在表模型、日期表基础上的，而SQL和Tableau则无需单独的日期表，基于窗口计算的逻辑实现偏移的查找、计算。

从这个计算也可以看出，Power BI“向左”——走向了更高级的、抽象的路线，Tableau“向右”——走向了交互、简洁的方向。二者并无优劣之分，只是适合不同的人群。

May 28, 2023 喜乐君