求累计值是最常用的计算之一，所有的累计都需要基于一个明确的排序规则，比如二八法则：所有产品的销售额从高到低排序，前 20%的产品累计占据了大约 80%的销售额。在所有排序规则中，时间又是最常用的一种，本文介绍如何计算基于时间的累计值
从本文开始你将接触时间智能函数的典型应用场景，关于函数的详细介绍会放到在这一章的后半部分。
关于时间的累计
年初至今(YTD)、季初至今(QTD)和月初至今(MTD)的计算都非常相似。在介绍计算方法前，从逻辑的角度你需要注意：累计是指定维度上值的加总，时间的累计就是在时间维度上计算当期值然后累加而来。比如年累计（YTD）是在年的维度上求值然后累加，跨年后重新计算。
图示：YTD 跨年后重新计算
所以月初至今只有在查看日级别的数据时才有意义，同样，年初至今只有在查看比年更低的时间粒度时才有意义，所以我们通常会使用年初至今累计和季初至今累计在月级别查看数据。下图中你可以看到按年、季度和月份合计的销售额。
销售金额按每行对应的日期区间汇总
通过将筛选上下文修改为从 1 月 1 日开始到与发生计算的单元格对应的月份结束，我们逐月计算了年初至今的销售额, 使用的 DAX 公式是:
- [YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD('Date'[Date] ) )
DATESYTD 函数返回从年初到当前筛选上下文中包含的最近日期的所有日期列表。这个列表被 CALCULATE 用作计算销售额的新筛选器。为了便于理解这个简化的写法，当此公式在 2007 年 3 月这个单元格计值的时候，DATESYTD 创建的日期列筛选器与下面这段代码相似
- [YTD Sales] :=
- CALCULATE (
- [Sales Amount],
- FILTER (
- ALL ( 'Date'[Date] ),
- AND (
- 'Date'[Date] >= DATE ( 2007, 1, 1 ),
- 'Date'[Date] <= DATE ( 2007, 3, 31 )
- )
- )
- )
在本章后续关于时间智能函数的专题文章中，你将了解有关 DATESYTD 函数底层代码更详细的说明。
下图可以看到 YTD 销售指标的计算结果。
YTD Sales 指标与常规销售指标同步变化
这种方法需要使用 CALCULATE 函数，但是由于这种模式(使用 CALCULATE 和 DATESYTD)非常常见，DAX 提供了一个简化的 YTD 的函数，并使其更易于阅读：TOTALYTD。你可以使用 TOTALYTD 实现等价的表达式:
- [YTD Sales] := TOTALYTD ( [Sales Amount] , 'Date'[Date] )
TOTALYTD 将需要聚合的表达式作为第一参数，日期列作为第二参数。该行为与原始度量值相同，但 TOTALYTD 这个名称使公式的行为更加清晰。然而，学习原始度量值的 CALCULATE 语法行为也是有好处的，因为它允许你执行更高级的计算，你将在稍后的文章中学习到这种用法。
与 YTD 类似，你还可以使用内置函数定义 QTD 和 MTD，如下所示：
- [QTD Sales] := TOTALQTD ( [Sales Amount], 'Date'[Date] )
- [QTD Sales] := CALCULATE ( [Sales Amount], DATESQTD ( 'Date'[Date] ) )
- [MTD Sales] := TOTALMTD ( [Sales Amount], 'Date'[Date] )
- [MTD Sales] := CALCULATE ( [Sales Amount], DATESMTD ( 'Date'[Date] ) )
下图你可以看到在数据透视表中使用的年初至今累计和季初至今累计。请注意，如果你观察 QTD Sales 度量值，年度总计行的值等于当年最后一个季度的销售额。
YTD Sales、QTD Sales、Sales Amount 度量值对比
计算上一年的 YTD
你还可以将当前年度的 YTD 数据与前一年同期的 YTD 结果做对比，当需要计算前一年 YTD 时，可以混合使用目前学到的两种技术。这时你不能将 Date[Date]参数（它对应于当前筛选下文中活动的日期列表）直接传递给 SAMEPERIODLASTYEAR，而是需要使用 DATESYTD 函数对日期列表进行转换，先定义出当前年度下 YTD 使用的日期列表。但是，你也可以在不影响结果的情况下反转这些调用的顺序。以下两种关于 PY YTD Sales 的定义是等价的，即使第二种可能会稍微快一些(但几乎不可检测)。
- [PY YTD Sales] := CALCULATE ( [Sales Amount],
- SAMEPERIODLASTYEAR ( DATESYTD ( 'Date'[Date] ) ) )
- [PY YTD Sales] := CALCULATE ( [Sales Amount],
- DATESYTD ( SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) )
你可以使用返回上一年日期列表的公式作为计算 YTD 度量值的筛选器参数，或者将返回 YTD 时间列表的公式作为计算去年同期度量值的筛选器参数，你可以使用这两种方式定义 PY YTD Sales，它们与之前的两个公式等价。
- [PY YTD Sales] := CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
- [PY YTD Sales] := CALCULATE ( [PY Sales], DATESYTD ( 'Date'[Date] ) )
你可以在图 7-19 中看到 PY YTD Sales 的结果，YTD Sales 的值是前者平移一年的结果，在相同的报告中，你还可以看到在上一节末尾使用的 Fiscal YTD Sales 指标。请注意，财年 YTD 的计算在每年的第三季度重新开始
图 7-19 上一年的 YTD 数据和财年数据可以出现在同一张报告中
计算财年
由于财年通常不会在 12 月 31 日结束，要计算一个财年的年初至今数据你必须使用一个可选的第三参数，它用于指定财年的结束日期。例如，你可以使用以下方法之一来计算销售额的财年至今累计值：
- [Fiscal YTD Sales] := TOTALYTD ( [Sales Amount], 'Date'[Date], "06-30" )
- [Fiscal YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "06-30" ) )
最后一个参数对应 6 月 30 日，即财政年度的结束日期。以下时间智能函数同样具有这样一个可选的年终日期参数：STARTOFYEAR, ENDOFYEAR, PREVIOUSYEAR, NEXTYEAR, DATESYTD, TOTALYTD, OPENINGBALANCEYEAR 和 CLOSINGBALANCEYEAR.
日期格式设置
公式中的日期格式取决于你本机的语言设置，某些情况下你可能需要将日放在前面：
- [Fiscal YTD Sales] := TOTALYTD ( [Sales Amount], 'Date'[Date], "30-06" )
- [Fiscal YTD Sales] :=CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date], "30-06" ) )
案例文件下载
计算累计.xlsx
(4.43 MB, 下载次数: 0)
|
[复制链接]
