DAX权威指南4：时间智能计算

一、 简介

1.1 日期表

时间智能是DAX中处理日期相关计算的重要功能，其核心依赖于日期表。日期表需满足以下条件：

• 包含连续日期（无缺失）。
• 与事实表通过日期列建立关系。
• 包含年、季度、月等分层结构列，便于分析。

为什么需要专用日期表？

• 避免重复计算：从日期列直接提取年月（如YEAR([Date])）会导致性能问题，且无法支持复杂时间计算。
• 统一分析口径：单个日期表可关联多个事实表（如订单日期、交货日期），确保时间筛选的一致性。
• 支持DAX时间智能函数：如TOTALYTD、SAMEPERIODLASTYEAR等函数要求模型存在日期表。

1.2 自动日期功能

1.2.1 Power BI的“自动日期/时间”功能

  “自动日期/时间”是 Power BI Desktop 中的一个数据加载选项，为用户提供了便捷的日期维度处理方式。当启用此功能时，Power BI 会自动为导入表中的日期列生成隐藏的日期表。在应用场景比较简单时，可以直接使用层级结构进行筛选、分组、向下钻取、使用时间智能，而无需手动创建日期表。

  你可以在全局或当前文件中配置此选项。启用此功能的条件是，表存储模式为“导入”，列数据类型为“日期”或“日期/时间”，列不是模型关系中的“多”方。开启后，Power BI Desktop 会为每个日期列创建一个隐藏的自动日期/时间表。这些表使用 CALENDAR生成，并包含六个计算列：Day,MonthNo,Month,QuarterNo,Quarter,Year。自动日期/时间表定义了层次结构，为视觉对象提供年份、季度、月份和日期级别的向下钻取路径。当 Power BI 刷新模型时，自动日期/时间表也会被刷新，以确保模型始终包含日期列值的完整日期范围。

  自动日期/时间表是隐藏的，无法直接在字段窗格或模型视图中看到，报表作者可以通过展开带有日历图标的字段来访问层次结构，并在视觉对象中使用这些层次结构。

使用 Power BI Desktop 编写的公式可以按常规方式引用日期列，然后使用.引用自动日期/时间表中的列：

1.2.2 Excel Power Pivot的自动日期列：

  Excel 中 的 Power Pivot 也具有自动创建数据结构的功能，即自动创建四个计算列，包括年，季度，月份名称，以及月份编号（排序所需），但是它使用的技术要比Power BI 差。缺点是增加存储开销（尤其是大数据量表），无法使用一个计算列来分割不同类型的日期（如同时分析订单和交货日期）。可以在 Excel 选项中禁用此功能：

1.2.3 Excel Power Pivot的日期表模板

  自2017年开始，Excel 中 Power Pivot 增加了一个可以创建日期表的选项。通过菜单一键生成可编辑的日期表，支持自定义列和模板保存，比上节介绍的功能更加优化：

  先将数据加载到数据模型，在弹出的Power Pivot界面中，单击设计选项卡-日期表- “New” ，将在模型中创建一个新表，其中包含一组包含年，月和工作日的计算列。 开发人员可以在模型中创建正确的关系集。 此外，如果需要，可以修改计算列的名称和公式，也可以添加新列。将当前表保存为新模板，将来可用于新创建的日期表。

二、 建立日期表

2.1 日期表建立准则

1. 包含所有相关日期
   日期表必须包含分析期间的所有日期。例如，如果销售数据起止日期是 2016 年 7 月 3 日到2019 年 7 月 27 日，那么日期表的范围应为 2016 年 1 月 1 日至 2019 年 12 月 31 日。日期序列中不能有缺失日期，即使某些日期没有交易记录，也必须包含在表中。

2. 使用正确的数据类型
   日期表应包含一列 DateTime 类型，并且该列应具有唯一值。Date 数据类型是更好的选择，因为它能保证时间部分为空。如果使用 DateTime 类型，表中一天中的所有时间必须相同。

3. 可以使用整型列来关联两个表，但需要存在 Date-Time 列。

4. 标记为日期表
   虽然这不是强制性步骤，但将表标记为日期表有助于编写正确的代码，并且只有这样才可以使用时间智能函数

  如果现有数据源中的日期表已经满足需求，可以直接加载。如果需要额外的日期描述列或调整数据结构，优先在数据源中通过增加计算列或更改查询来实现。如果数据源中没有日期表，或者现有日期表无法满足需求，可以使用DAX动态创建日期表，但可能会对模型性能产生一定影响。

2.2 使用 CALENDAR 和 CALENDARAUTO 创建日期表

CALENDAR 函数语法为：

CALENDAR(<start_date>, <end_date>)

以下代码创建了一个包含 Sales 表中所有日期的简单日期表：

-- 提取最小和最大年份，强制包含从 1 月 1 日到 12 月 31 日的所有日期。Date =
CALENDAR (DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1, 1 ),DATE ( YEAR ( MAX ( Sales[Order Date] ) ), 12, 31 )
)

CALENDARAUTO 函数会扫描整个数据模型中的所有日期列，引用的最小和最大年份，然后生成这些年之间的日期集，更简单地创建日期表：

-- 不用找到一年中的第一天和最后一天，因为 CALENDARAUTO 会在内部处理这个问题。
Date = CALENDARAUTO ()

然而，CALENDARAUTO 可能会计算不必要的日期列来扩展日期集，比如客户的生日。为了避免这种情况，可以通过以下代码限制日期集：

Date =
VAR MinYear =YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear =YEAR ( MAX ( Sales[Order Date] ) )
RETURNFILTER (CALENDARAUTO (),YEAR ( [Date] ) >= MinYear&& YEAR ( [Date] ) <= MaxYear)

CALENDARAUTO有一个唯一的参数，用于控制财年的起始月份。例如，以下代码创建从 7 月 1 日开始到 6 月 30 日结束的会计年度日期表：

Date = CALENDARAUTO ( 6 ) 

2.3 添加日期属性列

  虽然从技术角度来看，包含所有必需日期的单个日期列的表就足够了，但用户通常希望按年、月、季度等日期属性进行分析。因此，一个好的日期表应该包含一组丰富的列。以下是一个示例代码，通过 ADDCOLUMNS 函数添加了多个日期属性列，如年份、季度、月份等。

Date =
VAR MinYear =YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear =YEAR ( MAX ( Sales[Order Date] ) )
RETURNADDCOLUMNS (FILTER (CALENDARAUTO (),YEAR ( [Date] ) >= MinYear&& YEAR ( [Date] ) <= MaxYear),"Year", YEAR ( [Date] ),"Quarter Number", INT ( FORMAT ( [Date], "q" ) ),"Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),"Month Number", MONTH ( [Date] ),"Month", FORMAT ( [Date], "mmmm" ),"Week Day Number", WEEKDAY ( [Date] ),"Week Day", FORMAT ( [Date], "dddd" ),"Year Month Number", YEAR ( [Date] ) * 100+ MONTH ( [Date] ),"Year Month", FORMAT ( [Date], "mmmm" ) & " "& YEAR ( [Date] ),"Year Quarter Number", YEAR ( [Date] ) * 100+ INT ( FORMAT ( [Date], "q" ) ),"Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-"& YEAR ( [Date] ))

点击DAX Date Template，可下载Power BI日期模板文件。

2.4 处理多个日期列和日期表

  在实际的数据模型中，可能存在多个日期列，例如订单日期、到期日期和交货日期。在这种情况下，有两种设计选项：使用单个日期表但建立多个日期关系；或者干脆创建多个日期表。

2.4.1 使用一个日期表

  可以在两个表之间创建多个关系，但只有一种关系是活跃的。其他关系需要保持非活跃状态。可以通过 USERELATIONSHIP修饰符在 CALCULATE中激活非活跃关系。例如：

Ordered Amount :=
SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )Delivered Amount :=
CALCULATE (SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)

  这段代码定义了两个度量：订购金额和已交付金额。第一个度量使用活跃关系，第二个度量通过 USERELATIONSHIP 激活非活跃关系。

  此方式需要为每个日期字段创建不同的度量值，这可能会导致度量值过多。 通常，只定义需要的度量，并考虑使用计算组。

2.4.2 使用多个日期表

  另一种方法是为每个日期字段创建一个独立的日期表。例如，为订单日期创建一个日期表，为交货日期创建另一个日期表。此方法灵活性高，可以自由地选择使用哪个日期表进行分析，减少度量值数量。但是多个日期表可能导致查询性能下降，尤其是在数据量较大时。

  仅仅为了减少模型中的度量值数量而选择多个日期表是不合适的。因为在这种情况下，你无法创建一个同时按两个日期分组的报表。比如，如果你想制作一个折线图，既要按订单日期显示销售额，又要按交货日期显示销售额，这就需要在图表的日期轴中使用一个统一的 Date 表。而使用多个日期表的模式，很难实现这种需求，因为每个日期表只能代表一个日期维度，无法同时满足两个日期维度的展示。

  如果减少模型中度量值的数量是你的主要目标，并且你希望用户能够灵活地按任意日期维度查看任意度量值，那么应该考虑使用“计算组”。多个日期表最有用的场景是当你需要在可视化中交叉显示不同日期维度下的相同度量值时。例如，在一个矩阵报表中，你可以同时展示订单日期和交货日期下的销售额对比，这种场景下使用多个日期表是比较合适的。

• 在DAX中，“Date”是一个特殊的保留字，因为它和DATE函数同名。所以如果把日期表命名为“Date”，在引用它的时候就需要用双引号把它包起来
• 不同的日期表使用不同的命名，比如 表名为Order Date和Delivery Date；列名为Order Year 和 Delivery Year 。还可以根据日期的角色放置年份的前缀，例如使用 CY 前缀作为 Order Year 列的内容，使用 DY 前缀作为 Delivery Year 列的内容。

  但在大多数情况下，使用单个日期表并建立多个关系是更好的选择。这种方法既能保持模型的简洁性，又能通过 USERELATIONSHIP 函数灵活切换日期维度，同时避免了多个日期表带来的复杂性和潜在的性能问题。

三、时间智能计算

  时间智能计算的核心在于通过时间维度对数据进行分析和处理。DAX 提供了一系列时间智能函数，帮助用户轻松地进行复杂的日期和时间相关计算，如年初至今（YTD）、同比（YoY）等。

3.1 使用标准 DAX 函数实现时间智能计算（YTD）

  在没有直接使用时间智能函数的情况下，我们可以通过标准 DAX 函数（如 CALCULATE、CALCULATETABLE、FILTER 和 VALUES）来构建时间智能计算。这种方法虽然更复杂，但有助于理解时间智能计算的底层逻辑。而且许多非标准日历的时间计算，也需要手动实现计算逻辑。

  假设我们有一个 Sales 表与一个 Date 表，年初至今的销售额可写成：

Sales Amount YTD :=
VAR LastVisibleDate =			-- 获取当前筛选上下文中最后一个可见日期MAX ( 'Date'[Date] )		
VAR CurrentYear =				-- 提取该日期的年份YEAR ( LastVisibleDate )
VAR SetOfDatesYtd =				-- 创建一个日期集合，包含从年初到当前日期的所有日期FILTER (ALL ( 'Date' ),AND ( 'Date'[Date] <= LastVisibleDate, YEAR ( 'Date'[Date] ) = CurrentYear ))
-- 将 Sales Amount 的计算上下文修改为 SetOfDatesYtd，从而计算年初至今的销售额。
VAR Result =					CALCULATE ( SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), SetOfDatesYtd )
RETURNResult

3.2 日期表中DAX的特殊行为

  当筛选涉及日期列时，DAX会​​自动将隐式ALL应用于整个日期表​​，清除日期表上的其他列筛选（如年份、月份），以简化时间智能计算（无需手动清除）​。理解这一特殊行为对正确编写时间相关度量至关重要。

  还是以上一节的代码举例，如果仅对 Date[Date] 应用筛选器，其它部分不变，即代码改为：

VAR SetOfDatesYtd =FILTER (ALL ( 'Date'[Date] ),AND ( 'Date'[Date] <= LastVisibleDate, YEAR ( 'Date'[Date] ) = CurrentYear ))

  仔细分析这段代码。如果单元格的筛选上下文是 2007 年 4 月，那么执行的代码相当于：

CALCULATE (CALCULATE ([Sales Amount],AND ( 										-- This filter 'Date'[Date] >= DATE ( 2007, 1, 1 ), 	-- to the result'Date'[Date] <= DATE ( 2007, 04, 30 ) 	-- function )),'Date'[Year] = 2007,  							-- These are coming'Date'[Month] = "April" 						-- of the matrix 
) 

  在嵌套CALCULATE中，先计算外层筛选器​​（2007 年 4 月），再计算内层筛选器（2007/1/1 至 2007/4/30）。​​同一列的筛选器内层会覆盖外层，而​​不同列的筛选器会互相叠加。在这个例子中，内部 CALCULATE 的筛选器和外部 CALCULATE 的筛选器分别作用在不同的列上（Date 列和 Year、Month 列），最终筛选上下文应该是2007 年 4 月。

  然而，DAX 的特殊行为是当对日期列应用筛选器时，会自动将 ALL 添加到整个日期表。这意味着，即使我们只对 Date 列应用了筛选，DAX 也会清除整个日期表的其他筛选 。因此，上述实际上等同于：

CALCULATE (CALCULATE ([Sales Amount],AND ( -- This filter is equivalent to the result'Date'[Date] >= DATE ( 2007, 1, 1 ),  -- of the FILTER 'Date'[Date] <= DATE ( 2007, 04, 30 ) -- function),ALL ( 'Date' )),'Date'[Year] = 2007,  -- These are coming from the rows'Date'[Month] = "April" -- of the matrix in April 2007
)

1. ​先计算外层筛选器​：Year=2007 和 Month=April 最初限制上下文为 2007 年 4 月。
2. 后计算内层筛选器：
   • ALL(‘Date’) 显式清除所有 Date 表的筛选（包括外层的 Year 和 Month）
   • Date[Date] 列被筛选为为 2007/1/1 至 2007/4/30
3. ​​最终筛选器：最终筛选上下文仅保留日期范围​​，计算的是 2007 年前四个月的累计销售额

  结论：在 DAX 中，当你对日期表的日期列（Date 或 DateTime 类型）应用筛选器时，DAX 会自动清除日期表上其他列的筛选器，并将筛选器传播到整个日期表。这种行为的目的是简化时间智能计算，确保在计算如年初至今（YTD）或同比（YoY）等指标时，不会受到其他列筛选器的干扰。

  假设我们使用 DateKey（整数格式，如 YYYYMMDD）而不是日期列来连接 Sales 表和 Date 表。在这种情况下，DAX 不会自动添加 ALL，相同的代码会得出错误的结果：

  由于关系不再基于 DateTime列，因此 DAX 不会自动将 ALL 函数添加到日期表中。按上述推理，内外层筛选器叠加之后，筛选上下文就是当前月，所以Sales Amount YTD和 Sales Amount 的值相同。

  在这种情况下，有两种可能的解决方案：一种是手动将 ALL 添加到所有时间智能计算中；另一种是将 Date 表标记为日期表。一旦被标记为日期表，任何对日期列的筛选上下文修改都会自动应用 ALL，清除整个日期表的筛选器。

四、 时间智能计算介绍

传统上，要实现一个YTD（年初至今）计算，我们需要编写复杂的FILTER表达式：

Sales Amount YTD :=
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR CurrentYear = YEAR ( LastVisibleDate )
VAR SetOfDatesYTD =FILTER (ALL ( 'Date'[Date] ),AND ( 'Date'[Date] <= LastVisibleDate, YEAR ( 'Date'[Date] ) = CurrentYear ))
VAR Result = CALCULATE ( SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), SetOfDatesYTD )
RETURNResult

而使用DAX提供的DATESYTD函数，同样的计算可以简化为：

Sales Amount YTD :=
CALCULATE(SUMX(Sales, Sales[Net Price] * Sales[Quantity]),DATESYTD('Date'[Date])
)

  DATESYTD 与上一段的 FILTER 代码执行完全相同的功能，从性能到代码行为都是一样的。
  简单的计算，如 YTD ，QTD ，都可以用简单的时间智能函数来实现。复杂一点的计算通常可以通过混合使用标准时间智能函数来实现。只有极少数情况下需要开发人员编写时间智能函数，例如需要非标准的日历，比如周日历。

4.1 函数简介

1. 返回时间区间的函数

函数名	描述
DATESMTD, DATESQTD, DATESYTD	本月至今 、本季度至今 、 本年至今的日期范围
FIRSTDATE, LASTDATE	返回第一个日期 、返回最后一个日期
PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR	返回上一日、上一个月、上一个季度、上一个年度
NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR	返回下一日、下一个月、下一个季度、下一个年度
ENDOFMONTH , ENDOFQUARTER ,ENDOFYEAR	返回所属月度的最后一天、季度的最后一天、年度的最后一天
STARTOFMONTH, STARTOFQUARTER, STARTOFYEAR	返回所属月度的第一天、季度的第一天、年度的第一天
SAMEPERIODLASTYEAR	上年同期
DATEADD	移动一定间隔后的时间段
DATESBETWEEN	返回从起始日到结束日的时间段
DATESINPERIOD	返回从指定日期移动一定间隔的时间段
PARALLELPERIOD	返回移动指定间隔的完整粒度的时间段

  以上函数中，前20个函数只需要一个日期参数，一般结合CALCULATE使用，返回对应的时间期间。

2. 执行运算的函数

类型	函数	注释
累计计算	TOTALMTD, TOTALQTD, TOTALYTD	返回月初至今、季初至今、年初至今的累计值
余额计算	CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR	返回该月最后一天、该季度最后一天、该年度最后一天的数据
	OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR	返回上月最后一天、上季度最后一天、上年最后一天的数据

  这9个函数更加智能，不仅可以重置上下文，甚至直接对重置后的下上文执行运算，把CALCULATE都省掉了。比如求年初至今的销量，使用DATESYTD可以写作：

= CALCULATE([数量],DATESYTD(日期表[日期]))

TOTALYTD函数语法为：

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

此度量值可简写为：

= TOTALYTD（[数量],日期表[日期])

但凡是需要用到 CALCULATE 的地方，最好将其显式表达出来，比如它意味着上下文转换。

4.2 XX至今

年初迄今(YTD)、季初迄今(QTD)和月初迄今(MTD)这三种计算非常相似，主要区别在于计算的时间范围：

-- YTD计算
Sales Amount YTD := CALCULATE([Sales Amount], DATESYTD('Date'[Date]))
-- 简化版YTD
YTD Sales := TOTALYTD([Sales Amount], 'Date'[Date])-- QTD计算
QTD Sales := TOTALQTD([Sales Amount], 'Date'[Date])
QTD Sales := CALCULATE([Sales Amount], DATESQTD('Date'[Date]))-- MTD计算
MTD Sales := TOTALMTD([Sales Amount], 'Date'[Date])
MTD Sales := CALCULATE([Sales Amount], DATESMTD('Date'[Date]))

对于财政年度不同于自然年的情况，可以通过第三个参数指定财政年度结束日期：

Fiscal YTD Sales := TOTALYTD([Sales Amount], 'Date'[Date], "06-30")
Fiscal YTD Sales := CALCULATE([Sales Amount], DATESYTD('Date'[Date], "06-30"))

  以下几个年度智能函数，均可通过最后一个参数来设置年终日期：STARTOFYEAR、ENDOFYEAR、PREVIOUSYEAR、NEXTYEAR、DATESYTD、TOTALYTD、OPENINGBALANCEYEAR和CLOSINGBALANCEYEAR。

财政年度从 3 月开始到次年 2 月结束，则情况会比较复杂（闰年问题）后文会讲。

由于文化差异（区域设置），您可能需要使用日期编号，比如使用格式为 YYYY-MM-DD 的字符串：

Fiscal YTD Sales := TOTALYTD ( [Sales Amount], 'Date'[Date], "30-06" )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "30-06" ) )
Fiscal YTD Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "2018-06-30" ) )

4.2 同期比较

DATEADD到DATESINPERIOD这四个函数的参数复杂一点，一般用于相对时间区间的控制：

函数名	函数语法	参数说明
DATEADD	DATEADD(<dates>,<number>,<interval>)	1. 日期列 2. 整数（正数表示向未来移动，负数表示向过去移动） 3. 移动的粒度（可以是 year,quarter,month,day）
DATEBETWEEN	DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)	1. 日期列 2. 起始日期 3. 结束日期（包含结束日期当天）
PARALLELPERIOD	PARALLELPERIOD(<dates>,<number>,<interval>)	1. 日期列 2. 整数（正数表示向未来移动，负数表示向过去移动） 3. 移动的粒度（可以是 year,quarter,month,day）
DATESINPERIOD	DATESINPERIOD(<dates>, <start_date>, <intervals>, <interval>)	1. 日期列 2. 开始日期 3. 整数（正数表示向未来移动，负数表示向过去移动） 4. 移动的粒度（可以是 year,quarter,month,day）

// 计算年初至今，相当于 TOTALYTD
CALCULATE([数量], DATESBETWEEN('日期表'[日期], STARTOFYEAR('日期表'[日期]), LASTDATE('日期表'[日期])))// 计算上年全年的销量。
CALCULATE([数量], PARALLELPERIOD('日期表'[日期], -1, YEAR))// 返回过去7天的日期
DATESINPERIOD('日期表'[日期], MIN('日期表'[日期]), -7, DAY))// 组合计算：每月最后7天的销售额
CALCULATE([数量], DATESINPERIOD('日期表'[日期], ENDOFMONTH('日期表'[日期]), -5, DAY))

4.2.1 DATEADD：返回部分周期

去年同期(PY)计算：

PY Sales := CALCULATE([Sales Amount], SAMEPERIODLASTYEAR('Date'[Date]))

SAMEPERIODLASTYEARDATEADD的特殊情况，是更通用的方法是使用DATEADD函数：

PY Sales := CALCULATE([Sales Amount], DATEADD('Date'[Date], -1, YEAR))
PQ Sales := CALCULATE([Sales Amount], DATEADD('Date'[Date], -1, QUARTER))
PM Sales := CALCULATE([Sales Amount], DATEADD('Date'[Date], -1, MONTH))
PD Sales := CALCULATE([Sales Amount], DATEADD('Date'[Date], -1, DAY))

4.2.2 PARALLELPERIOD：返回完整周期

  PARALLELPERIOD函数返回完整的周期，而不只是转换后的部分周期。例如，即使当前筛选上下文只选择了一个月，PARALLELPERIOD也返回上一年的全年数据。

PY Total Sales := CALCULATE([Sales Amount], PARALLELPERIOD('Date'[Date], -1, YEAR))
PQ Total Sales := CALCULATE([Sales Amount], PARALLELPERIOD('Date'[Date], -1, QUARTER))

  其他类似函数，PREVIOUSYEAR 、PREVIOUSQUARTER、PREVIOUSMONTH、PREVIOUSDAY、NEXTYEAR、NEXTQUARTER、 NEXTMONTH和NEXTDAY，则返回所选周期的前一个相邻元素。假设选择了 2008 年第二季度（ 4、5、6月），以下代码中PM Total Sales返回所选月的上一个完整周期（3、4、5月）；而Last PM Sales只返回3月：

PM Total Sales := CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )
Last PM Sales := CALCULATE ( [Sales Amount], PREVIOUSMONTH( 'Date'[Date] ) )

4.2.3 计算同比

1. 计算去年同比：对于 Sales Amount 来说，今年与去年的绝对差额（同比或 YOY ）是一个简单的减法。如果只希望在两个值都有值时才显示差异，则需要添加错误保险（ISBLANK）。在这种情况下，变量就比较有用，因为它可以避免两次计算相同的度量值：

   YOY Sales :=
   VAR CySales = [Sales Amount]
   VAR PySales = [PY Sales]
   VAR YoySales = IF(NOT ISBLANK(CySales) && NOT ISBLANK(PySales), CySales - PySales)
   RETURN YoySales--百分比差异
   YOY Sales% := DIVIDE([YOY Sales], [PY Sales])
   

2. 计算去年和今年的 YTD ：只需将 YTD Sales 和 PY YTD Sales 进行简单的相减：

   YTD Sales := 
   TOTALYTD ( [Sales Amount], 'Date'[Date] )
   PY YTD Sales :=
   CALCULATE ( [Sales Amount], DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) )
   YOY YTD Sales :=
   VAR CyYtdSales = [YTD Sales]
   VAR PyYtdSales = [PY YTD Sales]
   VAR YoyYtdSales =IF (NOT ISBLANK ( CyYtdSales ) && NOT ISBLANK ( PyYtdSales ),CyYtdSales - PyYtdSales)
   RETURNYoyYtdSales
   -- 计算比例
   YOY YTD Sales% := DIVIDE ( [YOY YTD Sales], [PY YTD Sales] )
   

4.4 时间智能函数的组合使用

  大多数时间智能函数的第一个参数通常是日期表中的日期列（如’Date’[Date]），但这只是简化写法。实际上，时间智能函数的第一个参数应该是一个表。当在有行上下文（如在CALCULATE函数中）使用时，引用的日期列会被自动转换为一个只包含被筛选的非重复值的表。

PY Sales := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) )

相当于：

PY Sales :=
CALCULATE ([Sales Amount],DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) )
)

  因为时间智能函数接受一个表，所以任何表表达式都可以用来代替表，包括另一个时间智能函数，所以时间智能函数可以组合使用。比如计算去年 YTD 的值，可以通过组合 SAMEPERIODLASTYEAR 和 DATEYTD 来实现。有趣的是，交换函数调用的顺序并不会改变结果：

PY YTD Sales := CALCULATE([Sales Amount], SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))PY YTD Sales := CALCULATE([Sales Amount], DATESYTD(SAMEPERIODLASTYEAR('Date'[Date])))

YTD Sales 和 PY Sales 度量值在之前已经定义过，所以可以使用 CALCULATE 将当前筛选上下文移动到不同的时间段，再引用之前的度量值：

PY YTD Sales := CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
PY YTD Sales := CALCULATE ( [PY Sales], DATESYTD ( 'Date'[Date] ) )

4.5 移动年度总计(MAT)和移动年度平均(MAA)

4.5.1 计算MAT

  消除销售季节性变化的另一种常用计算是移动年度总销售额（MAT），它考虑了过去 12 个月的销售总额。例如 2008 年 3 月的 MAT 销售额，即 2007 年 4 月至 2008 年 3 月的销售总额。最简单的方法是使用 DATESINPERIOD 函数，该函数返回特定时间段内的所有日期。

DATESINPERIOD(<dates>, 					-- 日期列或日期表<start_date>, 				-- 起始日期<number_of_periods>, 		-- 周期数量，可以是正数（未来）或负数（过去）<interval>)					-- 时间间隔，可以是YEAR、QUARTER、MONTH、DAY等

MAT Sales :=
CALCULATE ([Sales Amount],DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
)

使用 DATESINPERIOD 通常是移动年度总计算的最佳选择。出于教学目的，另外一种计算 MAT Sales 的定义是：

MAT Sales :=
CALCULATE ([Sales Amount],DATESBETWEEN ('Date'[Date],NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),LASTDATE ( 'Date'[Date] ))
)

  因为 DATESBETWEEN 在日级别工作，即使报表在月级别查询数据，代码也必须计算所需间隔的第一天和最后一天。获取最后一天的一种方法是使用 LASTDATE 函数。该函数类似于 MAX ，但它返回一个表而非值，所以可以作为其他时间智能函数的参数。

4.5.2 注意嵌套时间智能函数的正确调用顺序

在前面的例子中，我们使用下面的 DAX 表达式来检索移动年度总数的第一天：

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )

如果调换 NEXTDAY 和 SAMEPERIODLASTYEAR 的顺序，可以得到相同的行为：

SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) )

两者结果几乎总是相同的，但后者在期末可能产生错误的结果：

MAT Sales Wrong :=
CALCULATE ([Sales Amount],DATESBETWEEN ('Date'[Date],SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( 'Date'[Date] ) ) ),LASTDATE ( 'Date'[Date] ))
)

  该方法在到 2009 年 12 月 30 日为止的计算都是正确值。但是到了 12 月 31 日，结果出人意料地高。原因是2009-12-31的NEXTDAY是2010-1-1，该日期不在日期表范围内，所以NEXTDAY返回一个空表。同样的，SAMEPERIODLASTYEAR也返回一个空表。空值对于日期时间值而言，被视为 1899 年 12 月 30 日，所以最终筛选上下文是整个日期表的日期范围。

  在将时间智能函数组合用于其他定制计算时，这个概念会尤为重要。对于移动年度总数的具体示例，使用 DATESINPERIOD 更简单、更安全。

4.5.3 计算MAA

将MAT的值除以时间段中包含的月数，即得到移动年平均（MAA）：

MAA Sales :=
CALCULATE(DIVIDE([Sales Amount], DISTINCTCOUNT('Date'[Year Month])),DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -1, YEAR)
)

五、 使用起止日期函数进行半累加计算

• 累加度量（Additive Measures）：可通过简单求和实现任意维度切片（如销售额）
• 非累加性度量：不可直接求和（如性别）
• 半累加性度量（Semi-Additive Measures）：在某些维度上可以进行累加，其它维度上则需要采用不同的聚合逻辑。一个典型的例子是银行账户余额。所有客户的余额可以进行累加，但全年的余额并不是每月余额的总和，而是年末最后一天的余额。

5.1 余额问题

5.1.1 使用LASTDATE计算余额：无法处理空值情况

  DAX 提供了一些函数来处理半累加性计算。以银行账户余额为例，可以考虑使用LASTDATE进行计算，它返回当前筛选上下文中最后一个可见日期。我们可以使用以下公式计算最后余额：

LastBalance := CALCULATE ( SUM ( Balances[Balance] ), LASTDATE ( 'Date'[Date] ) )

  如上图所示，LASTDATE的问题是：如果最后一天没有数据，则计算结果为空。

5.1.2 使用LASTNONBLANK计算余额：无法正确处理总计

LASTNONBLANK返回表中最后一个非空值对应的日期，可以避免空值情况。其语法为：

LASTNONBLANK(<table>, <expression>)

使用 LASTNONBLANK 扫描日期表以搜索 Balances 表中有数据的最后一个日期：

LastBalanceNonBlank :=
CALCULATE (SUM ( Balances[Balance] ),			LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( Balances ) ) )
)

  最内层的 RELATEDTABLE 函数在 LASTNONBLANK 迭代器的行上下文中执行，因此 RELATEDTABLE 只返回行上下文中活动日期的余额。如果没有数据，则 RELATEDTABLE 返回一个空表，COUNTROWS 返回一个空值。迭代结束时，LASTNONBLANK 返回计算结果非空的最后一个日期。

  上面各个客户的结果是正确的，但是当不同客户有不同的最后结余日期时，它无法正确计算总数。上图Total的结果是1950，而不是三个人余额的总和。

  当筛选上下文没有筛选客户名时，LASTNONBLANK 会查找所有客户中的最后一个有值的日期。在这个例子中，Maurizio Macagno 的最后结余日期是 2023-07-18，这是所有客户中最后的日期。因此，LASTNONBLANK 会返回 2023-07-18 作为最后日期，计算的总余额只会包括 Maurizio Macagno 的余额，即 1950。

  与LASTDATE 和 LASTNONBLANK功能类似的函数，还有FIRSTDATE 和 FIRSTNONBLANK。

5.1.3 代码优化

有两种方式处理这个问题：

1. 统一最后日期：将所有客户的最后日期统一为所有客户有结余的最后一个日期，再进行计算。

   LastBalanceAllCustomers :=
   VAR LastDateAllCustomers =CALCULATETABLE (LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( Balances ) ) ),ALL ( Balances[Name] )			-- 去掉了客户名中的筛选器)
   VAR Result =CALCULATE ( SUM ( Balances[Balance] ), LastDateAllCustomers )
   RETURNResult
   

   

   此方式只是计算正确，但不是我们最终需要的结果。比如Q3的结余，分了两天来统计。

2. 分别计算每个客户的最后结余：为每个客户计算其最后结余日期的值（计算每个客户的小计），然后汇总结果。

   LastBalanceIndividualCustomer :=
   SUMX (VALUES ( Balances[Name] ),CALCULATE (SUM ( Balances[Balance] ),LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( Balances ) ) ))
   )
   

   

     如果客户数很多，LastBalanceIndividualCustomer 度量可能存在性能问题。原因是该公式包含两个嵌套迭代器。在第 10 章“使用筛选上下文”中会介绍一种更快的解决方法，该方法将用到后面的章节中讨论的 TREATAS 等函数。

5.2 股票问题（期初和期末余额）

  STARTOFYEAR、STARTOFQUARTER、STARTOFMONTH 函数和 ENDOFYEAR、ENDOFQUARTER、ENDOFMONTH 函数可以表示起止日期 ，但这些函数都与 LASTDATE 有一样的限制——无法处理空值情况（空值日期返回BLANK())。

  假设我们有一个数据集，记录了微软股票从 2013 年至 2018 年的每日价格。在日级别上，使用 LASTDATE 函数计算最后一个值看起来是正确的：

Last Value := CALCULATE ( AVERAGE ( MSFT[Value] ), LASTDATE ( 'Date'[Date] ) )

  然而，当数据按年或月聚合时，如果月末没有数据（例如周末或节假日），LASTDATE 会返回一个空值，导致整个时间段的值为空。以上折线图看起来是正确的，是因为我们在 x 轴上使用了日级别，power BI 会自动忽略其中的空值，结果是一条连续的直线。但在在按年和月切片的矩阵中，空值会显示出来：

正确的方式应该使用 LASTNONBLANK：

Last Value :=
CALCULATE (AVERAGE ( MSFT[Value] ),LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( MSFT ) ) )
)

  假设需要计算从季度开始到现在的涨幅，一种方法是使用STARTOFQUARTER 。如果季度的第一天没有数据（例如节假日），同样会返回空值。

SOQ := CALCULATE ( AVERAGE ( MSFT[Value] ), STARTOFQUARTER ( 'Date'[Date] ) )
SOQ% := DIVIDE ( [Last Value] - [SOQ], [SOQ] )

正确的方法是使用FIRSTNONBLANK：

SOQ :=
VAR FirstDateInQuarter =CALCULATETABLE (FIRSTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( MSFT ) ) ),PARALLELPERIOD ( 'Date'[Date], 0, QUARTER ))
VAR Result =CALCULATE ( AVERAGE ( MSFT[Value] ), FirstDateInQuarter )
RETURNResult

六、 深入解析 DAX 时间智能计算

本节将深入探讨 DAX 中的高级时间智能计算，帮助读者理解其背后的原理，并掌握如何在实际场景中应用这些技术。

6.1 时间智能函数的上下文转换

DATESYTD 函数可以用来计算年初至今的日期范围，它可以等效于调用 CALCULATETABLE和 DISTINCT 获得的表：

DATESYTD ( 'Date'[Date] ) 
-- 对应于
DATESYTD ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ) ) 

在时间智能函数中，第一个参数引用日期列或整个日期表都可以，因为时间智能函数可以对第一个参进行上下文转换，更准确的内部逻辑如下：

-- 可以理解为，引用日期列时，在内部执行以下转换。如果是引用表，就没必要进行上下文转换
VAR LastDateInSelection =MAXX ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ), [Date] )
RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection ))

可简写为：

-- 先计算当前选择中的最后日期，然后创建一个筛选器，返回从年初到该日期的所有日期。
CALCULATETABLE (VAR LastDateInSelection = MAX ( 'Date'[Date] )RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection ))
)

类似地，DATESMTD ( 'Date'[Date] ) 等同于：

CALCULATETABLE (VAR LastDateInSelection =MAX ( 'Date'[Date] )RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection )&& MONTH ( 'Date'[Date] ) = MONTH ( LastDateInSelection ))
)

从以上代码可以看出：

1. 这些代码都是从当前选择的最后一天提取有关年，月和季度的信息。然后使用此日期创建一个合适的筛选器。
2. 如果不用时间智能函数，就需要像这样写一大堆公式。
3. 用通用 DAX 函数来实现时间智能函数的功能，都会需要包裹一个 CALCULATETABLE，目的其实有两个：
   • 时间智能函数返回一段日期区间，是个表，所以需要用CALCULATETABLE。
   • CALCULATETABLE 可以在这里执行上下文转换，是很有必要的。

6.2 上下文转换的必要性

  时间智能函数的一个关键特性是它们会自动进行上下文转换。例如，DATESYTD 函数在计算列中使用时，会自动应用上下文转换，从而返回从年初到当前行日期的天数。CountDatesYTD 的行为不同，由于没有上下文转换， CountFilter 计算最大日期后，总是检索整个日期表的最后日期，所以这样写是错的：

-- 在Date中创建两个计算列
'Date'[CountDatesYTD] = COUNTROWS ( DATESYTD ( 'Date'[Date] ) )
'Date'[CountFilter] =
COUNTROWS (VAR LastDateInSelection =MAX ( 'Date'[Date] )RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection ))
)

要从行上下文（比如计算列）中检索日期 ，使用变量而非 MAX 来检索当前行的日期值会更容易：

VAR CurrentDate = 'Date'[Date]
RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= CurrentDate&& YEAR ( 'Date'[Date] ) = YEAR ( CurrentDate ))

  反之，如果不在行上下文中执行，则可以删除外部 CALCULATETABLE 这个多余的操作。 比如经常在CALCULATE中使用DATEYTD 作为筛选器，而不是在迭代函数中使用它。此时， DATEYTD 函数可以重写为：

VAR LastDateInSelection =MAX ( 'Date'[Date] )
RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection ))

如果要使用不同的年终日期，比如财年从7月1日开始，可改写为：

-- 根据不同区域设置来应用：
DATESYTD ( 'Date'[Date], "06-30" )
DATESYTD ( 'Date'[Date], "30-06" )  

这等价于：

VAR LastDateInSelection =MAX ( 'Date'[Date] )
RETURNFILTER (ALL ( 'Date'[Date] ),'Date'[Date]'Date'[Date] <= LastDateInSelection &&> DATE ( YEAR ( LastDateInSelection ) - 1, <month>, <day> )             )

6.3 深入理解DATEADD

6.3.1 DATEADD应用规则

  DATEADD 函数用于检索经过某个偏移量后的日期。其基本语法如下。三个参数分别表示日期列或日期表、偏移量（正数向后、负数向前）、偏移的时间单位（年月日等）

DATEADD ( <dates>, <number>, <interval> )

  DATEADD 函数在处理日期时有一些特殊的行为，这些行为使得它在某些情况下比手动编写的 DAX 公式更加高效。为了更直观的感受DATEADD的规则，我们先创建以下度量值：

Day count := COUNTROWS ( 'Date' )		-- 计算所选天数PM Day count := CALCULATE ( [Day count], DATEADD ( 'Date'[Date], -1, MONTH ) ) 	-- 计算上个月的天数PM Range := 							-- 返回 DATEADD 选择的日期范围
CALCULATE (VAR MinDate = MIN ( 'Date'[Date] )VAR MaxDate = MAX ( 'Date'[Date] )VAR Result = FORMAT ( MinDate, "MM/DD/YYYY - " ) & FORMAT ( MaxDate, "MM/DD/YYYY" )RETURNResult,DATEADD ( 'Date'[Date], -1, MONTH )
)

1. 仅返回日期列中存在的天数：2007年之前的日期不在日期表中，所以上个月的日期返回空值。DAX 内部的多个时间智能函数自动在内部使用 DATEADD，因此拥有完整的日期表对 DAX 时间智能函数至关重要。
   
2. 自动处理月末数据：每个月份的天数是不一样的， DATEADD会自动将约莫数据与上个月月末数据对应。比如2007-3-28到2007-3-31这三天都对应于2007-2-28这一天。
   
3. 选择多个日期时，如果包括月末日期，则偏移后的天数可能不一样。比如下图选了2007-6-29到2007-7-1三天数据，上月同期是四天。这一点其实是第二条的延申——月末日期自动对应。
   

如果公式在月级别上计算，则结果是完全符合预期的：

6.3.2 DATEADD 清除日期列上任何现有过滤器

  将过滤器应用于 Date 表的日期列时，会在 Date 表本身上生成一个隐式的 ALL 操作，此 操作会清除 Date 表上除日期列之外的所有其他列（包括工作日列）上的现有过滤器。

  因此，假设你在报表中使用了工作日的过滤器（例如，通过一个切片器），并且同时使用了 DATEADD 函数，那么 DATEADD 函数可能会覆盖并忽略工作日的过滤器，从而导致结果不符合预期。

考虑以下 PM Sales DateAdd 定义，显示上个月的 Sales Amount：

PM Sales DATEADD := CALCULATE ( [Sales Amount], DATEADD ( 'Date'[Date], -1, MONTH ) )

  PM Sales DATEADD与上个月的Sales Amount结果不一样，是因为DATEADD会清除工作日过滤器（图中的切片器选择）。根据规则三，如果上个月末是周末（非工作日），DATEADD偏移后会将其也计算进去，导致结果比Sales Amount更大。

  简单说就是Sales Amount只计算工作日数据，而DATEADD会清除其它过滤器，导致本月即使都是工作日，因为月末偏移的原因，也会对应上上月的非工作日。

如果你需要考虑工作日的过滤器，可以编写自定义的 DAX 公式，使用 FILTER 函数来手动筛选工作日的日期而非DATEADD 函数：

-- 定义一个计算列，用于生成年月编号
-- 例如，2023年1月的年月编号为 2023 * 12 + 1 - 1 = 24275
Date[YearMonthNumber] = 
'Date'[Year] * 12 + 'Date'[Month Number] - 1 -- 定义一个度量，用于计算上个月的工作日销售额
PM Sales Weekday := 
VAR CurrentMonths =DISTINCT ( 'Date'[YearMonthNumber] ) -- 获取当前选择的年月编号
VAR PreviousMonths =TREATAS ( -- 将上个月的年月编号应用为筛选条件SELECTCOLUMNS ( -- 选择当前年月编号并减去1，得到上个月的年月编号CurrentMonths, "YearMonthNumber", 'Date'[YearMonthNumber] - 1 ),'Date'[YearMonthNumber] -- 将上个月的年月编号应用到日期表的年月编号列)
VAR Result =CALCULATE ( -- 计算上个月的工作日销售额[Sales Amount], -- 要计算的销售额度量ALLEXCEPT ( 'Date', 'Date'[Week Day] ), -- 保留工作日的过滤器，清除其他日期相关的过滤器PreviousMonths -- 应用上个月的年月编号筛选条件)
RETURNResult 

扩展一下，使用 ISFILTERED 函数，可以根据用户是否应用了工作日过滤器，动态选择合适的计算逻辑：

-- 使用 ISFILTERED 函数检查过滤器在[Day of Week]列中是否处于活动状态
PM Sales := 
IF (ISFILTERED ( 'Date'[Day of Week] ),[PM Sales Weekday],[PM Sales DateAdd]
)

6.4 深入理解FIRSTDATE、LASTDATE、FIRSTNONBLANK、LASTNONBLANK

6.4.1 FIRSTDATE、LASTDATE

  FIRSTDATE 和 LASTDATE 仅在日期列上运行，它们分别返回活动筛选上下文中的第一个和最后一个日期，忽略其它相关表中存在的任何数据，其行为有点像 MIN 和 MAX 。但有一个重要区别：FIRSTDATE 和 LASTDATE 返回一个表并执行上下文转换，而 MIN 和 MAX 返回标量值，也不进行任何上下文转换。

FIRSTDATE ( 'Date'[Date] ) 
LASTDATE ( 'Date'[Date] )

假设我们有一个名为 Inventory 的表，其中包含库存数量和日期。以下表达式计算最后一个日期的库存总和：

CALCULATE ( SUM ( Inventory[Quantity] ), LASTDATE ( 'Date'[Date] ) )

考虑上下文转换，可重写为：

CALCULATE (SUM ( Inventory[Quantity] ),VAR LastDateInSelection =MAXX ( CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ), 'Date'[Date] )RETURNFILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] = LastDateInSelection )
)

  所以，在编写筛选表达式时（CALCULATE、CALCULATETABLE函数的过滤器参数中），使用 FIRSTDATE / LASTDATE 更好，因为语法更简单。但在需要标量值的行上下文中编写逻辑表达式时（比如FILTER 函数中），MIN / MAX 函数更好，因为 FIRSTDATE / LASTDATE 隐含的上下文转换可能会改变结果。

  例如，在计算累计库存量时，可使用以下公式获取截止当前筛选上下文的所有日期：

FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )

  如果在迭代函数FILTER中使用LASTDATE，由于隐式的上下文转换，它会将当前的行上下文（当前迭代的日期）转换为等效的筛选上下文，于是 LASTDATE(‘Date’[Date]) 在每次迭代中返回的其实是当前行的日期值（因为筛选上下文仅包含当前行）。每一行都会满足条件，最终返回所有日期。

FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= LASTDATE ( 'Date'[Date] )--这个条件总为真)

6.4.2 FIRSTNONBLANK、LASTNONBLANK

FIRSTNONBLANK、LASTNONBLANK是迭代器，其语法为：

LASTNONBLANK(<column>,<expression>)

这两个函数会遍历的每一行，并检查第二个参数的表达式是否非空，然后返回第一个/最后一个非空值（非空日期）。

由于第二个参数通常是 度量引用 或 包含 CALCULATE 的表达式，因此也会触发上下文转换：

LASTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( Inventory ) ) )

其等效公式为：

LASTNONBLANK ( 'Date'[Date], COUNTROWS ( RELATEDTABLE ( Inventory ) )

LASTNONBLANK 是迭代器，对大数据集可能较慢，需谨慎使用。如果只需要最后一个有数据的日期，可以用 MAXX + FILTER 组合：

MAXX ( FILTER ( 'Date', COUNTROWS ( RELATEDTABLE ( Inventory ) ) > 0 ),'Date'[Date]
)

可用于查找其他表的最后一个有效值（如最后一个下单的客户）：

LASTNONBLANK ( Customers[Name], CALCULATE ( COUNTROWS ( Orders ) ) )

特性	FIRSTDATE/LASTDATE	FIRSTNONBLANK/LASTNONBLANK
输入类型	仅限日期列（DateTime 或 Date）	任意数据类型的列（如文本、数字等）
是否迭代器	否（直接返回列的最小/最大日期）	是（逐行检查第二个参数是否非空）
是否依赖上下文转换	否（但隐式转换可能影响结果）	是（第二个参数通常依赖 CALCULATE）
典型用途	返回日期列的范围（不考虑数据是否存在）	返回有实际数据的最后一个日期（如库存、销售）

6.5 时间智能钻取

  时间智能函数在钻取操作中可能会表现出不符合预期的行为。这是因为钻取操作通常不会考虑度量本身定义的筛选上下文中的更改。相反，它仅考虑由数据透视表的行、列、过滤器和切片器定义的筛选上下文。

  以2007 年 3 月为例，通过使用 TOTALYTD，你期望得到 2007 年 1 月至 3 月的所有日期；通过使用 SAMEPERIODLASTYEAR，期望得到 2006 年 3 月；通过使用 LASTDATE，期望得到 2007 年 3 月 31 日的行。但实际上，在默认钻取中，所有这些过滤器都返回 2007 年 3 月的所有行。

  这种行为可以通过 Tabular 模型中的 Detail Rows 属性进行控制。Detail Rows 属性必须应用与相应时间智能度量相同的过滤器。例如，对于年初至今的度量，其 Detail Rows 属性应设置为：

-- YTD度量：
CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ))-- 对应Detail Rows 属性：
CALCULATETABLE ( Sales, DATESYTD ( 'Date'[Date] ) )

  在Analysis Services 2017 或 Azure Analysis Services 数据模型中可以设置 Detail Rows 属性，但截至 2019年 4 月，Power BI 或 Power Pivot for Excel 中不可用。

七、自定义日历

  DAX 的标准时间智能函数主要基于格里高利日历（基于太阳历，分为 12 个月），适用于大多数常规的时间分析需求。然而，当面对非标准日历时，这些函数就显得力不从心。

  为了在 DAX 中实现自定义日历的时间智能计算，一个常见的技术是通过在日期表中添加专用列来存储与自定义日历相关的属性。这种方法的优点是可以在不修改度量的 DAX 代码的情况下，仅通过更改日期表的内容来调整关联规则。

7.1 建立ISO周日历

DAX 不提供任何处理周的时间智能函数，因为周的变化比较复杂，一周会跨年度，季度，和月份。不过，你可以使用以下计算列扩展 Date 表以支持 ISO 周：

'Date'[Calendar Week Number] = WEEKNUM ( 'Date'[Date], 1 ) 'Date'[ISO Week Number] = WEEKNUM ( 'Date'[Date], 21 ) 'Date'[ISO Year Number] = YEAR ( 'Date'[Date] + ( 3 - WEEKDAY ( 'Date'[Date],3)))'Date'[ISO Week] = "W" & 'Date'[ISO Week Number] & "-" & 'Date'[ISO Year Nember]'Date'[ISO Week Sequential] = INT ( ( 'Date'[Date] - 2 ) / 7 ) 'Date'[ISO Year Day Number] = 
VAR CurrentIsoYearNumber = 'Date'[ISO Year Number]
VAR CurrentDate = 'Date'[Date]
VAR DateFirstJanuary =DATE ( CurrentIsoYearNumber, 1, 1 )
VAR DayOfFirstJanuary =WEEKDAY ( DateFirstJanuary, 3 )
VAR OffsetStartIsoYear = - DayOfFirstJanuary + ( 7 * ( DayOfFirstJanuary > 3 ) )
VAR StartOfIsoYear = DateFirstJanuary + OffsetStartIsoYear
VAR Result = CurrentDate - StartOfIsoYear
RETURNResult

  这些列的添加使得日期表能够存储与 ISO 周相关的属性，从而支持基于周的时间智能计算。例如，ISO Week Number 列表示 ISO 周的编号，ISO Year Number 列表示 ISO 年份，而 ISO Year Day Number 列则表示自 ISO 年度开始以来的天数。

7.2 ISO日历计算

  基于扩展后的日期表，我们可以编写自定义的时间智能计算公式。例如，以下是一个计算年初至今（YTD）销售额的度量：

ISO YTD Sales := 
IF (HASONEVALUE ( 'Date'[ISO Year Number] ),VAR LastDateInSelection = MAX ( 'Date'[Date] )VAR YearSelected = VALUES ( 'Date'[ISO Year Number] )VAR Result =CALCULATE ([Sales Amount],'Date'[Date] <= LastDateInSelection,'Date'[ISO Year Number] = YearSelected,ALL ( 'Date' ))RETURNResult
)

  这个度量首先检查是否只选择了一个 ISO 年份，然后计算从 ISO 年度开始到当前选择日期的销售额。通过使用 ISO Year Number 列，我们可以确保计算基于 ISO 年度而不是传统的格里高利年份。

  下图 显示了 2008 年年初至今的结果。与通过 DATESYTD 计算的标准 YTD 相比， ISO 版本准确包括 2007 年 12 月 31 日，它属于 ISO 2008 年：

7.3 周计算

基于之前创建的ISO周日历，我们可以实现基于周的时间智能计算。例如，以下是一个计算上一年同期销售额的度量：

ISO PY Sales := 
IF (HASONEVALUE ( 'Date'[ISO Year Number] ),VAR DatesInSelection = VALUES ( 'Date'[ISO Year Day Number] )	-- 当前年日编号VAR YearSelected = VALUES ( 'Date'[ISO Year Number] )			-- 当前年度VAR PrevYear = YearSelected - 1									-- 上一年度VAR Result =CALCULATE ([Sales Amount],DatesInSelection,										-- 上年同一年日编号'Date'[ISO Year Number] = PrevYear,ALL ( 'Date' ))RETURNResult
)

CAL PY Sales = CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR( 'Date'[Date] ) ) 

  这个度量通过比较当前选择的 ISO 年日编号与上一年的相同编号来计算上一年同期的销售额。这种方法利用了 ISO 年日编号的规律性，使得基于周的比较变得简单。

  当你了解了不同年份的相同日期之间的对称性之后，基于周的日历将很容易管理。这通常与日历月不兼容，因此如果要使用这两个层次结构（月和周），则应为每个层次结构创建不同的时间智能计算。

7.4 自定义年初迄今、季初迄今及月初迄今

除了基于周的计算，我们还可以通过类似的方法实现自定义的年初迄今、季初迄今及月初迄今的计算。例如，YTD Sales 度量为：

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

使用DAX函数可重新写为：

YTD Sales :=
VAR LastDateInSelection =MAX ( 'Date'[Date] )
VAR Result =CALCULATE ([Sales Amount],'Date'[Date] <= LastDateInSelection&& YEAR ( 'Date'[Date] ) = YEAR ( LastDateInSelection ))
RETURNResult

如果不使用日期列，比如使用Calendar Year Number列，使用自定义逻辑可实现相同的功能：

YTD Sales Custom := 
VAR LastDateInSelection =MAX ( 'Date'[Date] )
VAR LastYearInSelection =MAX ( 'Date'[Calendar Year Number] )
VAR Result =CALCULATE ([Sales Amount],'Date'[Date] <= LastDateInSelection,'Date'[Calendar Year Number] = LastYearInSelection,ALL ( 'Date' ))
RETURNResult

同样的方法可以应用于季初迄今和月初迄今的计算，只需将 Calendar Year Number 替换为相应的季度或月份列即可：

QTD Sales Custom := 
VAR LastDateInSelection =MAX ( 'Date'[Date] )
VAR LastYearQuarterInSelection =MAX ( 'Date'[Calendar Year Quarter Number] )
VAR Result =CALCULATE ([Sales Amount],'Date'[Date] <= LastDateInSelection,'Date'[Calendar Year Quarter Number] = LastYearQuarterInSelection,ALL ( 'Date' ))
RETURNResult

MTD Sales Custom := 
VAR LastDateInSelection =MAX ( 'Date'[Date] )
VAR LastYearMonthInSelection =MAX ( 'Date'[Calendar Year Month Number] )
VAR Result =CALCULATE ([Sales Amount],'Date'[Date] <= LastDateInSelection,'Date'[Calendar Year Month Number] = LastYearMonthInSelection,ALL ( 'Date' ))
RETURNResult