求累计值是最常用的计算之一,所有的累计都需要基于一个明确的排序规则,比如二八法则:所有产品的销售额从高到低排序,前 20%的产品累计占据了大约 80%的销售额。在所有排序规则中,时间又是最常用的一种,本文介绍如何计算基于时间的累计值
从本文开始你将接触时间智能函数的典型应用场景,关于函数的详细介绍会放到在这一章的后半部分。
关于时间的累计
年初至今(YTD)、季初至今(QTD)和月初至今(MTD)的计算都非常相似。在介绍计算方法前,从逻辑的角度你需要注意:累计是指定维度上值的加总,时间的累计就是在时间维度上计算当期值然后累加而来。比如年累计(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 销售指标的计算结果。
这种方法需要使用 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
你还可以将当前年度的 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 的计算在每年的第三季度重新开始
计算财年
由于财年通常不会在 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" ) )
高老师,文中计算上年累计的等效度量值:
[PY YTD Sales] := CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )的计值顺序是如何的?
我理解是先计算外层cal的筛选器参数SAMEPERIODLASTYEAR ( ‘Date'[Date] ) ,得到去年的同期日期1,然后计算表达式[YTD Sales],而[YTD Sales]也是一个度量值,先计算其筛选器参数,得到当年累计日期2的筛选,此时日期2会覆盖日期1的筛选吗?那结果就不是去年累计了。
请问,在POWER PIVOT 里使用时间公式,YTD 有值,QTD MTD WTD均显示为空白是什么原因呢?
公式如下:
业绩total:=SUM('订单'[销售业绩])
QTD:=CALCULATE([业绩total],DATESQTD(‘Calendar'[日期]))
高老师,我在网上看到以下公式可以算累计值。我套用了,也成功了。但是没有搞懂逻辑,为什么小于最大值max()就是累计值。请赐教。
Cumulative Total =
CALCULATE (
SUM(‘Global-Superstore'[Sales]),
FILTER (
ALL( ‘Global-Superstore'[Order Date] ),
‘Global-Superstore'[Order Date] <= MAX ( 'Global-Superstore'[Order Date] )
)
)
你好,请问度量值MTD和QTD为什么出现空白,YTD没有问题,期待回复谢谢!