什么是移动总计
移动总计又称滚动总计,英文 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)的移动年度总计相同。
嵌套函数的调用顺序问题
当你调用嵌套的时间智能函数的时候,你可能想知道正确的调用顺序是什么。在前面的例子中,我们使用下面的 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 创建了一个覆盖整个日期表的计算。
2009年有数据,2010没有数据。用MAT Sales度量值时,比如2010年一季度,往前推一年应该有数据才对,为什么得不到结果?
怎么解决日期表没有数据的问题呢?