【DAX 系列】PowerBI 按年季月日分层级智能计算期初期末

BI佐罗

发布于 2020-02-26 11:46:19

2.2K0

发布于 2020-02-26 11:46:19

文章被收录于专栏：PowerBI战友联盟 PowerBI战友联盟PowerBI战友联盟

期初期末是一个非常常见的计算需求，但很多人表示没有完美的解决方法，其实不然。我们先来看下期初期末的难点在哪里。如下所示：

参考上图左右两边，左边的每日的明细，可以理解为每日的日末；右边给出了期末的计算困境以及需求。（期初类似）

完美需求是：当在矩阵的不同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。

总结

本文给出了计算期初期末的通用算法，并做到：

正确处理了空单元值
任意层级智能切换计算
巧妙设计了层级代码技巧

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-02-16，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自 PowerBI战友联盟微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

编程算法

登录后参与评论

0 条评论

热度