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

计算累计值 YTD,QTD,MTD

求累计值是最常用的计算之一,所有的累计都需要基于一个明确的排序规则,比如二八法则:所有产品的销售额从高到低排序,前 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, 31, 1 )
        )
    )
)

在本章后续关于时间智能函数的专题文章中,你将了解有关 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 度量值对比

计算财年

要计算一个财年的年初至今数据,由于财年通常不会在 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" ) )

说点什么

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

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组函数

连接函数

集合函数

其他常见函数