本文来自伙伴从实际案例的问题。在 PowerBI 中，时间智能计算是一个老生常谈的问题，但在实际中可能会出现各种变种，这就要求我们灵活处理。
先来看问题是怎样的：
[图片上传失败...(image-6b879-1571722027209)]
同样是 MTD 的去年同期，会出现上下两种情况。请仔细观察后发现，我们需要的是上面这种。原因如下：
- 2019年（当前年）的未来月份还没到来，不应该进行计算对比。
- 2019年10月（当前月份）还没过完，应该按过完的日期区间同比去年同期而不是全年的整月。
MTD 的标准计算
这里给出标准计算：
-- 定义 KPI ，值得注意的是，这里考虑的单位。
KPI.Sales = [Core.Sales] / [Core.Unit]
-- 定义 KPI 的 MTD，如下：
KPI.Sales.MTD = CALCULATE( [KPI.Sales] , DATESMTD( Model_Calender[日期] ) )
使用嵌套定义 KPI 的方式是复用的标准用法。
MTD 的默认去年同期
继续基于已经定义好的 MTD 来计算去年同期，也很简单，如下：
-- 我们有一些对 DAX 编写的良好简称约定，例如：PY 指的是 Previous Year 即去年同期
KPI.Sales.MTD.PY =
CALCULATE( [KPI.Sales.MTD] , SAMEPERIODLASTYEAR( Model_Calender[日期] ) )
至此，我们就可以得到一个结果，如下：
image
可以看出，这种计算将得到开篇的错误形式，严格来说，从技术的逻辑来看是没有错误的，然而从业务的逻辑来看，它不符合常识。
正确计算 MTD 的去年同期
从业务的角度来计算 MTD 的去年同期，就应该满足开篇的条件：
- 2019年（当前年）的未来月份还没到来，不应该进行计算对比。
- 2019年10月（当前月份）还没过完，应该按过完的日期区间同比去年同期而不是全年的整月。
为此，我们定义如下的计算规则：
-- 当前数据的最后日期：
Date.LastDate.All = MAXX( ALL( Model_Order ) , [订单日期] )
-- 最后一个年月：
Date.LastYearMonth = YEAR( [Date.LastDate.All] ) * 100 + MONTH( [Date.LastDate.All] )
-- MTD 的去年同期：
V.MTD:Sales.MTD.PY = // 考虑当月不完全结束月，同比
VAR vCurrYearMonth = SELECTEDVALUE( Model_Calender[年月序号] )
VAR vLastYearMonth = [Date.LastYearMonth]
RETURN SWITCH( TRUE(),
vCurrYearMonth < vLastYearMonth , [KPI.Sales.MTD.PY] ,
vCurrYearMonth = vLastYearMonth ,
CALCULATE( [KPI.Sales.MTD.PY] ,
TREATAS( { [Date.LastDate.All] } , Model_Calender[日期] ) ) ,
BLANK()
)
从其中可以看出，在最后一个数据月之前，按照常规方式计算；在最后一个数据月按照最后一天来计算。因此，得到：
image
这样，就得到了正确的结果。
大部分高手做到这里就OK了，但此时其实隐藏了一个很细很细的 BUG，看你是否可以看出来？
修复细微的 BUG
怎么会有 BUG 呢？如果你仔细思考，并在大脑中一天天的过，你会发现，如果当月达到最后一天时，便成了满月，一旦变成满月就应该与去年的同期满月对比，这里就存在一个闰年2月的情况，例如：当日是 2017-02-28，应该同比 2016-02-29，因此应该特别处理。
修改此前的公式为：
Date.ClosedMonthDate =
VAR X = [Date.LastDate.All] -- 实际日期的最后一天
RETURN IF( X = EOMONTH( X , 0 ) , X , EOMONTH( X , -1 ) ) -- 如果本月已满，则返回本月月底日否则返回上月月底日
Date.IsClosedMonth = IF( MAX( Model_Calender[日期] ) <= [Date.ClosedMonthDate] , TRUE() , FALSE() )
V.MTD:Sales.MTD.PY.Fixed =
VAR vCurrYearMonth = SELECTEDVALUE( Model_Calender[年月序号] )
VAR vLastYearMonth = [Date.LastYearMonth]
RETURN SWITCH( TRUE(),
vCurrYearMonth < vLastYearMonth , [KPI.Sales.MTD.PY] ,
vCurrYearMonth = vLastYearMonth ,
IF( [Date.IsClosedMonth] , // 如果达到满月
[KPI.Sales.MTD.PY] , // 满月按正常计算
CALCULATE( [KPI.Sales.MTD.PY] , // 非满月按最后一天计算
TREATAS( { [Date.LastDate.All] } , Model_Calender[日期] ) )
) ,
BLANK()
)
这样就得到最准确的计算。
就在文章发布的时候又有了新的方法，如下：
V.MTD:Sales.MTD.PY.Fixed2 =
// 考虑当月不完全结束月，同比
// 如果当月正好过完最后一天，那么对当月而言，应该同比去年的整月，对于闰二月的情况，将相差1日，例如：当日是 2017-02-28，应该同比 2016-02-29，因此应该特别处理。
VAR vCurrentLastDate = MIN( Model_Calender[日期] )
VAR vLastDateInData = [Date.LastDate.All]
RETURN
IF ( vCurrentLastDate <= vLastDateInData ,
IF( [Date.IsClosedMonth] , [KPI.Sales.MTD.PY] , CALCULATE( [KPI.Sales.MTD.PY] , TREATAS( { vLastDateInData } , Model_Calender[日期] ) ) )
)
总结
MTD 并不是一个复杂的问题，默认计算也是正确的，但对于业务常识来看，可能存在一定差异，因此，我们就需要调整某些度量值的计算。一个通用的套路是：
- 先按常规的计算；
- 再按照业务实际调整；
- 最后复查边界条件。
本案例启发我们按层次定义度量值，可以在不同的场景进行复用，并且需要考虑边界条件以使得计算是更加准确的。示例文件已经共享至订阅会员星球，祝您玩得开心。
网友评论