PowerBI & Excel CEO 駕駛艙 - 第四彈

暮秋殘陽 2019-08-13

展開全文

從本文開始合并之前的三篇文章并入一個系列《PowerBI & Excel CEO 駕駛艙》。

第一彈 - PowerBI 日期區(qū)間

第二彈 - Excel 日期區(qū)間

第三彈 - PowerBI 相對日期區(qū)間

第四彈 - Excel 相對日期區(qū)間（也就是本文）

也許您從純辦公進(jìn)入PowerBI領(lǐng)域，在這個系列中，我們也會分享如果用極簡的工程式方法來不斷優(yōu)化迭代一個小項目。

本文主要是：【Excel版】PowerBI & Excel CEO 駕駛艙 - 0.2.00 - 190810 - 加入：相對日期XTD 的實現(xiàn)。

效果

也就是，用戶選擇：【截止到】【相對于】【XTD區(qū)間】系統(tǒng)應(yīng)該自動計算近期結(jié)果并與上期做出實時比對以幫助決策者快速制定調(diào)整策略。

這與【Excel版】的歷史分析一起配合，如下：

整個Excel文件也得到整理：

數(shù)據(jù)模型

在 Excel 數(shù)據(jù)模型中，如下：

除了此前的日期區(qū)間，本次加入相對日期區(qū)間。

相對日期區(qū)間

我們知道在 Excel 的數(shù)據(jù)模型中是無法實現(xiàn) 雙向篩選的，為此，我們的計算公式需要有些調(diào)整，如下：

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

這非常簡單，我們使用 CALCULATETABLE 來計算根據(jù)用戶實時選擇的區(qū)間，再計算 KPI。

對于相對應(yīng)的上期，如下：

KPI.PX.Period.XTD:=
VAR XTD = IF( HASONEVALUE( 'DatePeriod XTD'[區(qū)間類型] ) , VALUES( 'DatePeriod XTD'[區(qū)間類型] ) )
RETURN SWITCH( TRUE(),
    XTD = '當(dāng)日' , 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()
)

感謝楊總指出這里根本不應(yīng)該讓用戶選擇對比的上期，而應(yīng)該根據(jù)XTD的類型自動匹配到合理的上期。

我們放大其中的一個邏輯如下：

IF ( XTD = '當(dāng)日' )

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

與當(dāng)日相對比的應(yīng)該是上一日，因此邏輯必然有：DATEADD( ‘日期’[日期] , -1 , DAY )。這里指的一提的是，由于在Excel的數(shù)據(jù)模型中無法使用雙向篩選，因此，我們需要借助CALCULATETABLE( '日期' , 'DatePeriod XTD' )給出正確的計算范圍。