从数据到信息
从信息到洞察

初识时间智能

引言

几乎所有的业务逻辑都会涉及到关于时间的计算,比如年累计销售额,月环比销售额,YOY(Year over Year)等等,DAX 提供专注解决此类问题的函数,当模型满足特定要求的时候,时间智能函数可以大大简化运算。

在本章中,通过学习常见的时间智能函数,你将掌握时间计算的奥秘,如年初至今累计、年同比和多个年份的对比等等,也包括非累加度量值半累加度量值,你将学习如何使用特定的时间智能函数计算这两种度量值。还将了解到如何在非标准日期表环境下使用自定义 DAX 公式以及实现基于周的计算。

时间智能函数可以简化运算 ,但这其中也蕴含了一些复杂性,这种复杂性隐藏在那些构成时间智能函数的基本函数中。是的,时间智能函数本身并不是底层函数,它们是由像 CALCULATE聚合函数这样的基本函数为实现特定的计算逻辑组合而成,为了避免每次输入冗长的公式,我们将其赋予一个通俗易懂,且容易使用的短名,这也是时间智能函数的由来。这类语法有一个更通用的名称:语法糖

本章将介绍常用时间智能函数背后的通用公式,它可以帮助你彻底理解时间智能函数,并在某些特定场景下规避副作用、用基础函数写出适用于特定情境的“时间智能”公式。

介绍日期表

数据模型通常会包含一个日期表。事实上,需要按年和月聚合数据时,使用日期表(每天对应一行)的列是更好的选择,而不是新建计算列从日期表的日期列或日期时间列中提取日期部分。这么做有几个原因:

  • 模型中的所有日期属性都包含在一个单独的表中, 可以更轻松地使用通用客户端浏览数据
  • 可以使用专门的 DAX 函数来执行时间智能计算。而且,大多数时间智能函数都需要连续日期才能正常工作,单独的日期表可以满足这个条件。

星型模型中定义单独的日期表是一种常见的做法。你应该对任何模型都使用这种技术,即便在开始还不是星形模型的时候。当需要分析日期列时,你需要创建一个日期列与日期表的关系。如果在一个表中有多个需要分析的日期列,那么除了单个活动关系外,你还可以创建连接到日期表的多个非活动关系,如下图中的销售表所示

销售表和日期表有一条活动关系,两条非活动关系

你还可以选择为每个日期列创建不同的日期表。在本章的后面,我们将讨论这两种选择。

无论如何,当数据中有一个或多个日期列时,都应该在模型中至少创建一个日期表

时间智能函数在钻取时的特殊行为

我们知道 Excel 透视表支持双击值区域的单元格查看明细数据,这个功能可以让你快速查看当前筛选条件下数据源的所有记录。但是,当使用时间智能函数时,公式都更改了日期表的筛选上下文,从而得到不同于初始筛选上下文的计值结果。在使用支持报表钻取操作的客户端(例如 Excel 中的数据透视表)时,你可以观察到意料之外的结果:明细数据只受到来自外部的筛选上下文的影响。原因是钻取操作由 MDX 执行,它不考虑度量值内部定义的筛选上下文,而只接受由透视表的行、列、过滤器和切片器定义的筛选上下文环境。

例如,对 2007 年 3 月的钻取始终返回这个时间段内的明细数据,与度量值应用的时间智能函数无关。比如

  • 使用 TOTALYTD 计算累计值,钻取后你预期返回 2007 年 1 月至 3 月的所有日期;
  • 使用 SAMEPERIODLASTYEAR 计算去年同期值,钻取后你预期返回 2006 年 3 月的所有日期,
  • 使用 LASTDATE,钻取后你预期只得到 2007 年 3 月 31 日所在行。

不幸的是,对以上度量值的钻取都只返回 2007 年 3 月的数据。而且,这种行为是故意设计的。

不仅是时间智能函数,所有通过 DAX 公式创建的筛选上下文都不能反应在钻取的结果中

时间智能函数一览

函数 说明
CLOSINGBALANCEMONTH 计算当前上下文中该月最后一个日期的表达式。
CLOSINGBALANCEQUARTER 计算当前上下文中该季度最后日期的表达式。
CLOSINGBALANCEYEAR 计算当前上下文中该年最后一个日期的表达式。
DATEADD 返回一个表,该表包含日期的列,按指定的时间间隔(从当前上下文中的日期开始向前或向后移动)。
DATESBETWEEN 返回一个表,该表包含以 start_date 开头并持续到 end_date 的日期列。
DATESINPERIOD 返回一个表,其中包含一个日期列,该列的日期从 start_date 开始,并继续指定的 number_of_intervals。
DATESMTD 返回一个表,该表包含当前上下文中的本月截止日期的列。
DATESQTD 返回一个表,该表包含当前上下文中的季度截止到现在的日期列。
DATESYTD 返回一个表,该表包含当前上下文中当前年份的日期列。
ENDOFMONTH 返回当前上下文中指定日期列的最后一个月的日期。
ENDOFQUARTER 返回当前上下文中指定日期列的季度最后一个日期。
ENDOFYEAR 返回当前上下文中指定日期列的年份的最后日期。
FIRSTDATE 返回当前上下文中指定日期列的第一个日期。
FIRSTNONBLANK 返回按当前上下文筛选的列列中的第一个值,其中表达式不为空
LASTDATE 返回当前上下文中指定日期列的最后日期。
LASTNONBLANK 返回按当前上下文筛选的列列中的最后一个值,其中表达式不为空。
NEXTDAY 返回一个表,其中包含从下一天起的所有日期的列,它基于当前上下文中日期列中指定的第一个日期。
NEXTMONTH 返回一个表,其中包含下个月中的所有日期的列,它基于当前上下文中日期列中的第一个日期。
NEXTQUARTER 返回一个表,其中包含下一季度中的所有日期的列,它基于当前上下文中 “日期” 列中指定的第一个日期。
NEXTYEAR 返回一个表,其中包含下一年的所有日期的列,它基于当前上下文中日期列中的第一个日期。
OPENINGBALANCEMONTH 计算当前上下文中该月第一个日期的表达式。
OPENINGBALANCEQUARTER 计算当前上下文中该季度第一个日期的表达式。
OPENINGBALANCEYEAR 计算当前上下文中该年度第一个日期的表达式。
PARALLELPERIOD 返回一个表,其中包含一个日期列,该日期表示与当前上下文中指定日期列中的日期并行的时间段,其中的日期在时间中向前或向后移动。
PREVIOUSDAY 返回一个表,其中包含表示当前上下文中日期列中第一个日期之前日期的所有日期的列。
PREVIOUSMONTH 返回一个表,该表包含上个月中的所有日期的列,该列基于当前上下文中日期列中的第一个日期。
PREVIOUSQUARTER 返回一个表,该表包含上一季度的所有日期的列,该列基于当前上下文中日期列中的第一个日期。
PREVIOUSYEAR 返回一个表,该表包含在当前上下文中的日期列中的最后一个日期之后的所有日期的列。
SAMEPERIODLASTYEAR 返回一个表,其中包含从当前上下文中指定日期列中的日期起返回一年的日期的列。
STARTOFMONTH 返回当前上下文中指定日期列的第一个月的日期。
STARTOFQUARTER 返回当前上下文中指定日期列的季度第一天的日期。
STARTOFYEAR 返回当前上下文中指定日期列的年份的第一个日期。
TOTALMTD 在当前上下文中计算本月截止日期的表达式的值。
TOTALQTD 计算当前上下文中的季度截止日期的表达式的值。
TOTALYTD 计算当前上下文中的表达式的年初至今值。
大部分时间智能函数都不能在 DirectQuery 模式下使用,你需要使用 CALCULATE 和基础函数重写表达式

说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
  订阅本文评论  
提醒
DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组函数

连接函数

集合函数

其他常见函数