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

计算移动总计

什么是移动总计

移动总计又称滚动总计,英文 Running Total 或 Moving Total,是计算累计的一种特殊方式,与 YTD 最大的不同在于,移动总计始终按固定的时间长度回溯,这种处理方式可以消除时间性的周期变化对数据的影响,基于这个固定的时间长度,你还可以计算移动平均、移动的最大值、最小值等等。

黄色的移动平均数据抵消了周期性波动

移动年度总计

移动年度总计 (Moving Annual Total 简称 MAT)是一种常见的用于消除销售季节性变化的计算方式,它总是考虑过去 12 个月的数据。例如,你可以通过计算 2007 年 4 月至 2008 年 3 月期间的日期范围来计算 2008 年 3 月的 MAT 销售数据。最简单的方法是使用 DATESINPERIOD 函数,它返回给定区间中的所有日期组成的单列形式的表,该函数应用偏移量(第三参数)到第一参数使用的日期列,返回第四参数所指定区间中包含的所有日期。

[MAT Sales] :=
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( '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 函数获得最后一天,该函数返回给定列的最后日期(始终考虑当前筛选上下文)。从这个日期开始,你可以通过请求一年前相应的那个最后日期的第二天(通过调用 NEXTDAY)来获得间隔的第一天。(通过 SAMEPERIODLASTYEAR 实现)

在图 7-21 中,你可以看到包含移动年度总计的报告。例如, Q1-2008 对应的 MAT 销售额是将 2007 年第二季度、2007 年第三季度、2007 年第四季度和 2008 年第一季度的销售额相加后得到的结果。在中间,你看到的是经典的年度至今计算公式(YTD),它的值与每年最后一个区间(在本例中是 Q4)的移动年度总计相同。

图 7-21 此报表使用的度量值仅显示有余额数据的天数

嵌套函数的调用顺序问题

当你调用嵌套的时间智能函数的时候,你可能想知道正确的调用顺序是什么。在前面的例子中,我们使用下面的 DAX 表达式来检索“移动的年度总计”周期的第一天:

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

在这种情况下,LASTDATE 返回 2008 年第一季度的最后一个日期(2008 年 3 月 31 日),SAMEPERIODLASTYEAR 返回 2007 年 3 月 31 日,NEXTDAY 返回 2007 年 4 月 1 日。你也可以颠倒 NEXTDAY 和 SAMEPERIODLTYYR 之间的调用顺序:

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

它返回相同的结果(2007 年 4 月 1 日),但有一个风险。如果日期表在 2011 年 12 月 31 日结束,并且你考虑的是 2011 年第四季度的结果,那么就会发生这样的情况:LASTDATE 返回 2011 年第四季度的最后一个日期(2011 年 12 月 31 日),NEXTDAY 返回 2012 年 1 月 1 日,SAMEPERIODLASTYEAR 应该返回 2011 年 1 月 1 日。但是,所有这些函数只能返回 Date[Date]列的现有值。如果日期表没有 2012 年的数据,则 NEXTDAY 调用无法返回 2012 年 1 月 1 日,它将改为返回空。例如,考虑以下错误的 MAT 计算:

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

如图 7-22 所示, 2011 年第四季度和 2011 年年度的计值结果都是错误的,因为 2010 年和 2011 年都没有销售记录。发生这种情况是因为 DATESBETWEEN 的初始日期为空,CALCULATE 创建了一个覆盖整个日期表的计算。

图 7-22 MAT Sales Wrong 在 2011 年第四季度和 2011 年全年计值了错误结果

7
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
3 评论数
6 被回复的评论
4 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
153****1882

2009年有数据,2010没有数据。用MAT Sales度量值时,比如2010年一季度,往前推一年应该有数据才对,为什么得不到结果?

成员
如鱼得水

怎么解决日期表没有数据的问题呢?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数