期初期末是一个非常常见的计算需求,但很多人表示没有完美的解决方法,其实不然。我们先来看下期初期末的难点在哪里。如下所示:
参考上图左右两边,左边的每日的明细,可以理解为每日的日末;右边给出了期末的计算困境以及需求。(期初类似)
完美需求是:当在矩阵的不同Level中,期末应该可以自动正确计算,如最后一列所示。
这里会遇到两个问题。
如上图,可以看到2020.5.31日无任何数据记录,如果按照期末计算,则返回空,这样就无法正确计算期末了。在季度和年度的级别也是类似的问题。
含有问题的月度期末计算公式:
KPI.EOM =
CALCULATE( [KPI] , ENDOFMONTH( Model_Calendar[Date] ) )
类似的,季度期末计算公式:
KPI.EOQ =
CALCULATE( [KPI] , ENDOFQUARTER( Model_Calendar[Date] ) )
我们已经说明了问题,因此,对其修复如下:
KPI.EOM.NoBlank = // 期间最后一个非空指标日的指标
CALCULATE(
[KPI] ,
CALCULATETABLE(
LASTNONBLANK( Model_Calendar[Date] , [KPI] ) ,
PARALLELPERIOD( Model_Calendar[Date] , 0 , MONTH )
)
)
我们仔细来看这个差异:
对于 EOM 的计算,在 5 月而言,由于5.31日没有数据,因此返回了空,对应的值就是空;而 EOM.Blank 的计算,在 5 月而言,则可以返回真正意义上实际最后一日的数据得到正确的结果。
不难看出,对于日,月,季,年,至少存在四个不同的度量值,那么如果是在一个带层次结构的矩阵里,如果随着层级的展开而自动计算在不同层级的期初期末呢。
下面给出通用算法:
KPI.EOP.NoBlank = // End Of Period - 任意期末
VAR LevelCode =
ISINSCOPE( Model_Calendar[年份序号] ) * 1000 +
ISINSCOPE( Model_Calendar[Quarter] ) * 100 +
ISINSCOPE( Model_Calendar[月份序号] ) * 10 +
ISINSCOPE( Model_Calendar[Date] )
RETURN SWITCH( LevelCode ,
1111 , [KPI] ,
1110 , [KPI.EOM.NoBlank] ,
1100 , [KPI.EOQ.NoBlank] ,
1000 , [KPI.EOY.NoBlank] ,
BLANK()
)
这个算法非常巧妙,它灵活地使用了 ISINSCOPE 并一次性给出层级代码,通过层级代码知道所处的层级,来计算对应的 KPI。
本文给出了计算期初期末的通用算法,并做到: