PowerBI & Excel CEO 驾驶舱 - 第四弹 -

作者: PowerBI战友联盟 | 来源:发表于2019-08-10 23:49 被阅读0次

从本文开始合并之前的三篇文章并入一个系列《PowerBI & Excel CEO 驾驶舱》。

第一弹 - PowerBI 日期区间

第二弹 - Excel 日期区间

第三弹 - PowerBI 相对日期区间

第四弹 - Excel 相对日期区间（也就是本文）

也许您从纯办公进入PowerBI领域，在这个系列中，我们也会分享如果用极简的工程式方法来不断优化迭代一个小项目。

本文主要是：【Excel版】PowerBI & Excel CEO 驾驶舱 - 0.2.00 - 190810 - 加入：相对日期XTD 的实现。

效果

也就是，用户选择：【截止到】【相对于】【XTD区间】系统应该自动计算近期结果并与上期做出实时比对以帮助决策者快速制定调整策略。

这与【Excel版】的历史分析一起配合，如下：

整个Excel文件也得到整理：

数据模型

在 Excel 数据模型中，如下：

除了此前的日期区间，本次加入相对日期区间。

相对日期区间

我们知道在 Excel 的数据模型中是无法实现双向筛选的，为此，我们的计算公式需要有些调整，如下：

KPI.Period.XTD:=CALCULATE( [KPI.Core] , CALCULATETABLE( '日期' , 'DatePeriod XTD' )  )

这非常简单，我们使用 CALCULATETABLE 来计算根据用户实时选择的区间，再计算 KPI。

对于相对应的上期，如下：

KPI.PX.Period.XTD:=
VAR XTD = IF( HASONEVALUE( 'DatePeriod XTD'[区间类型] ) , VALUES( 'DatePeriod XTD'[区间类型] ) )
RETURN SWITCH( TRUE(),
    XTD = "当日" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    XTD = "上一日" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    XTD = "WTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -7 , DAY ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    XTD = "MTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , MONTH ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    XTD = "QTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , QUARTER ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    XTD = "YTD" , CALCULATE( [KPI.Core] , CALCULATETABLE( DATEADD( '日期'[日期] , -1 , YEAR ) , CALCULATETABLE( '日期' , 'DatePeriod XTD' ) ) ) ,
    BLANK()
)

感谢杨总指出这里根本不应该让用户选择对比的上期，而应该根据XTD的类型自动匹配到合理的上期。

我们放大其中的一个逻辑如下：

IF ( XTD = "当日" )

CALCULATE( 
    [KPI.Core] , 
    CALCULATETABLE( 
        DATEADD( '日期'[日期] , -1 , DAY ) , 
        CALCULATETABLE( '日期' , 'DatePeriod XTD' ) 
    )
)

与当日相对比的应该是上一日，因此逻辑必然有：DATEADD( '日期'[日期] , -1 , DAY )。这里指的一提的是，由于在Excel的数据模型中无法使用双向筛选，因此，我们需要借助CALCULATETABLE( '日期' , 'DatePeriod XTD' )给出正确的计算范围。