PowerBI & Excel CEO 終極駕駛艙 - 第二彈 - 綜合近期與歷史分析

長期關(guān)注PowerBI戰(zhàn)友聯(lián)盟的戰(zhàn)友會發(fā)現(xiàn)，我們現(xiàn)在的很多文章出現(xiàn)了連載的跡象。我們在此前的文章以及系統(tǒng)化的視頻教程中已經(jīng)講解了PowerBI及DAX基礎(chǔ)部分，我們的文章將不斷基于這些基礎(chǔ)給出非?，F(xiàn)實(shí)的設(shè)計。每篇文章可能會以及此前的文章，并重點(diǎn)解決某類痛點(diǎn)，最后給出一個綜合的標(biāo)準(zhǔn)實(shí)現(xiàn)。

當(dāng)您下載到標(biāo)準(zhǔn)實(shí)現(xiàn)后，也許您直接看到這個設(shè)計是不容易理解的，因?yàn)樗皇屈c(diǎn)一點(diǎn)出來了，是基于大量基礎(chǔ)和優(yōu)化過的思想進(jìn)行的，需要您結(jié)合對應(yīng)文章提到的前序文章和實(shí)現(xiàn)來理解。本文繼續(xù)。

最終效果

CEO需要實(shí)時和歷史地追蹤和分析不同元素（可能是：人，產(chǎn)品，BU，市場等）的變化以進(jìn)行實(shí)時決策。

我們簡單解讀一下：對于銷售額，這里考察經(jīng)理楚杰在相對于報表刷新日期前一天（設(shè)為：基準(zhǔn)日）的業(yè)績表現(xiàn)；左邊是相對于基準(zhǔn)日的MTD的表現(xiàn)；右邊是歷史不同月份的表現(xiàn)；非常清晰地看到了楚杰對銷售額完成的全景。

這張圖表的信息量之大，信息密集度之高，用到PowerBI以及DAX技巧之妙，先讓我們有一個感覺。我們得到這樣的結(jié)果是一步步進(jìn)行的，下面仔細(xì)地來描述。

建議您在看下文時稍作停留，您可以觀察和思考下，上圖中可能涉及到的PowerBI或DAX的難點(diǎn)，如果您的思考都在后文里，說明您可以解構(gòu)一個設(shè)計了，當(dāng)然，能做到不看后文而完全解構(gòu)的可能性太小了。

前情回顧

對于CEO查看各種指標(biāo)，我們此前兩篇文章已經(jīng)給出了不錯的開始，可以參考：

《PowerBI 零基礎(chǔ)支持上百指標(biāo)多日期切換分析模板》以及《Excel CEO 終極駕駛艙 - 第一彈》。

在其中：

以及：

這很好。存在一個問題：

大部分 CEO 并不是在一個月（季度，周）結(jié)束時才關(guān)注目標(biāo)的進(jìn)展，他們需要隨時可以看到項目當(dāng)前的進(jìn)展。

舉個例子，周1開會，對于用戶增長這一指標(biāo)，往往知道本月的指標(biāo)，還需要知道到了周3，本周的周1到周3相對上周的周1到周3是否有所提升，如果沒有提升，說明策略很可能是有問題的，需要及時調(diào)整，不可以等到下周1再開會，更不可以等到下個月再開會。這可以定義為部分?jǐn)?shù)據(jù)驅(qū)動驅(qū)動的目標(biāo)管理。

再比如，預(yù)定的一個季度的銷售指標(biāo)，當(dāng)前的MTD需要與上月的MTD做及時的對比，而不是等到這個月結(jié)束再對比。

那么，在如上的歷史分析圖表中就無法應(yīng)對了。

因此，我們需要一種相對日期區(qū)間的分析，相對的參考點(diǎn)就是今天。從今天看：今天，昨天，本周至今，本月至今，本季至今，本年至今。

PowerBI 的不足

在給出我們正確的解決方案前，我們來看看在PowerBI中可以做什么，是否可以解決這個問題。在PowerBI中，如果拖拽日期維度進(jìn)入畫布，確實(shí)可以設(shè)置相對日期，如下：

我們打開日歷，來對照下：

我們會發(fā)現(xiàn)：PowerBI給出的日歷周是從周日開始的。這還不是最大的問題，最大的問題是這里的相對日期并沒有TD（To Date）的概念，例如：WTD指的是本周一至今；MTD指的是本月一日至今。

因此，PowerBI的相對日期區(qū)間比較受限。為此，我們需要構(gòu)建我們自己的相對日期架構(gòu)。

超級靈活的相對日期架構(gòu)XTD

除了不再重復(fù) PowerBI 的相對日期外，我們給出一套更有意義的相對日期XTD架構(gòu)設(shè)計。所謂XTD就是DTD，WTD，MTD，QTD，YTD。含義為：當(dāng)日，當(dāng)周1日至當(dāng)日，當(dāng)月1日至當(dāng)日，當(dāng)季1日至當(dāng)日，當(dāng)年1日至當(dāng)日。

值得強(qiáng)調(diào)的是：當(dāng)【X區(qū)間】的1日至當(dāng)日 中的 “當(dāng)” 字是非常精確的說法。不是本年至今，更不是本月至今。本年至今，從語義上看，是現(xiàn)實(shí)世界的時間；而我們所說的 當(dāng)期的1日至當(dāng)日 并不一定是現(xiàn)實(shí)世界的時間。如果您根本對此沒有感覺，那本文您將收獲頗豐；如果您完全明白我們的意思，說明您對日期智能（準(zhǔn)確講不是時間智能，而是日期智能）有了深入的思考和理解。

首先，我們看看效果，再做解釋：

可以看到，我們需要同時考慮的事情包括：

  • 相對于刷新日期還是現(xiàn)實(shí)日期。本例中，刷新日期：2019.07.14而現(xiàn)實(shí)日期是今日。
  • 截止到最后一日還是最后一日的上一日。在實(shí)際中，最后一日可能是數(shù)據(jù)不全的，因此可能需要上一日。
  • 區(qū)間類型是XTD。需要實(shí)現(xiàn)6種。
  • 同比上期。需要實(shí)現(xiàn)5種。

我們來看下DAX實(shí)現(xiàn)：

DatesPeriod.XTD = 
VAR vToday = TODAY()
VAR vLastUpdate = [System.LastUpdate]
VAR Period_Today = 
    VAR X   = TREATAS( { vToday } , 'DatesTemplate'[Date] )
    VAR PX  = TREATAS( { vToday - 1 } , 'DatesTemplate'[Date] )
    VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "區(qū)間類型" , "當(dāng)日" , "截止到" , "最后一日" , "區(qū)間類型排序" , 1 )
    VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "區(qū)間類型" , "當(dāng)日" , "截止到" , "上一日" , "區(qū)間類型排序" , 1 )
    VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "區(qū)間類型" , "上一日" , "截止到" , "最后一日" , "區(qū)間類型排序" , 2 )
    VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "區(qū)間類型" , "上一日" , "截止到" , "上一日" , "區(qū)間類型排序" , 2 )
    VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "區(qū)間類型" , "WTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 3 )
    VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "區(qū)間類型" , "WTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 3 )
    VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "區(qū)間類型" , "MTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 4 )
    VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "區(qū)間類型" , "MTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 4 )
    VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "區(qū)間類型" , "QTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 5 )
    VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "區(qū)間類型" , "QTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 5 )
    VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "區(qū)間類型" , "YTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 6 )
    VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "區(qū)間類型" , "YTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 6 )
    RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
VAR Period_LastUpdate = 
    VAR X   = TREATAS( { vLastUpdate } , 'DatesTemplate'[Date] )
    VAR PX  = TREATAS( { vLastUpdate - 1 } , 'DatesTemplate'[Date] )
    VAR TodayX = ADDCOLUMNS( LASTDATE( X ) , "區(qū)間類型" , "當(dāng)日" , "截止到" , "最后一日" , "區(qū)間類型排序" , 1 )
    VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "區(qū)間類型" , "當(dāng)日" , "截止到" , "上一日" , "區(qū)間類型排序" , 1 )
    VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "區(qū)間類型" , "上一日" , "截止到" , "最后一日" , "區(qū)間類型排序" , 2 )
    VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "區(qū)間類型" , "上一日" , "截止到" , "上一日" , "區(qū)間類型排序" , 2 )
    VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "區(qū)間類型" , "WTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 3 )
    VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "區(qū)間類型" , "WTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 3 )
    VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "區(qū)間類型" , "MTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 4 )
    VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "區(qū)間類型" , "MTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 4 )
    VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "區(qū)間類型" , "QTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 5 )
    VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "區(qū)間類型" , "QTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 5 )
    VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "區(qū)間類型" , "YTD" , "截止到" , "最后一日" , "區(qū)間類型排序" , 6 )
    VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "區(qū)間類型" , "YTD" , "截止到" , "上一日" , "區(qū)間類型排序" , 6 )
    RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )
RETURN 
    UNION( 
        ADDCOLUMNS( Period_Today , "相對日期" , "現(xiàn)實(shí)日期" ),
        ADDCOLUMNS( Period_LastUpdate , "相對日期" , "刷新日期" )
    )

這里不做過多解釋，因?yàn)檫@里的DAX并不復(fù)雜，僅僅用來拼裝這個結(jié)構(gòu)。得到：

再來看看數(shù)據(jù)模型。

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

對于日期的使用，您將看到史上近乎終極的日期模型：

對關(guān)鍵進(jìn)行說明：

  • 【必】不直接創(chuàng)建日期表，而是創(chuàng)建一個日期表模板。（原因不展開，記住就行）
  • 日期表 = 日期表模板。
  • 日期區(qū)間表，并與日期表構(gòu)建雙向篩選。
  • 日期區(qū)間表（XTD版），并與日期表構(gòu)建雙向篩選。

值得強(qiáng)調(diào)的是（以下針對高手）：

  • 日期表模板是必須的，避免對日期表的循環(huán)依賴。

如果您不明白上面的內(nèi)容，不要緊，照著做就可以了。

其實(shí)本案例的數(shù)據(jù)模型和日期模型并不復(fù)雜，只是要穿透這種復(fù)雜性，就需要超越這種復(fù)雜性去理解透徹日期表和篩選背后的本質(zhì)，這塊內(nèi)容太過技術(shù)，我們不再展開，最終形成的結(jié)構(gòu)卻是簡單的。

處理復(fù)雜的日期篩選

當(dāng)您使用度量值的時候，要非常清楚這個度量值受到三套日期結(jié)構(gòu)的篩選（如上圖）。為此，在構(gòu)建一個重要的度量值PX（上期）時，我們的精妙處理是這樣的：

KPI.PX = 
VAR vPeriodType = SELECTEDVALUE( DatesPeriod[區(qū)間類型] )
RETURN SWITCH( TRUE() ,
    vPeriodType = "年" , CALCULATE( [KPI] , SAMEPERIODLASTYEAR( Dates[Date] ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
    vPeriodType = "季" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , QUARTER ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
    vPeriodType = "月" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , MONTH ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
    vPeriodType = "周" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -7 , DAY ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
    vPeriodType = "日" , CALCULATE( [KPI] , DATEADD( Dates[Date] , -1 , DAY ) , ALL( DatesPeriod ) , ALL( 'DatesPeriod.XTD' ) ) ,
    BLANK()
)

即：需要用ALL取消對兩個日期區(qū)間表的篩選，這里涉及到對CALCULATE參數(shù)的精確理解，不再展開。但我們可以給出一個結(jié)論：使用CALCULATE可以構(gòu)建任意復(fù)雜的篩選結(jié)構(gòu)。這樣，我們就有了正確計算PX的度量值。

度量值

我們簡單解釋一下度量值的安排：

其實(shí)非常簡單：

KPI = 
VAR vKPIName = SELECTEDVALUE( 'KPI參數(shù)'[KPI名稱] )
RETURN SWITCH( TRUE() , 
    vKPIName = "銷售額" , [Core.Sales] ,
    vKPIName = "利潤" , [Core.Profit] ,
    vKPIName = "利潤率%" , [Core.Profit%] ,
    vKPIName = "數(shù)量" , [Core.Volume] ,
    [Core.Sales]
)

對于 KPI.PX 已經(jīng)給出，不再贅述。

PowerBI 的篩選控制

如果您親自實(shí)踐，會遇到奇怪的問題，我們簡單解釋一下您可能遇到的問題以及如何處理：

很明顯，上圖中紅色的篩選是一個整體；黃色的篩選是一個整體。而在實(shí)際中，他們會交叉影響的，這是PowerBI作為BI產(chǎn)品的交叉篩選的默認(rèn)特性。我們需要簡單進(jìn)行設(shè)置以實(shí)現(xiàn)定向的精確控制，如下：

對于每個篩選器，我們都要精確設(shè)置其影響的范圍。

什么鬼？沒見過？請系統(tǒng)化學(xué)習(xí)我們出品的《PowerBI商業(yè)智能分析基礎(chǔ)系列》。

能做好的設(shè)計的前提是非常堅實(shí)的基礎(chǔ)。

至此，整個設(shè)計就OK了。

用IBCS進(jìn)行標(biāo)準(zhǔn)化

這里直接使用了滿足IBCS商業(yè)圖表標(biāo)準(zhǔn)的ZebraBI for PowerBI做標(biāo)準(zhǔn)化展現(xiàn)，如下：

即使沒有這些插件，也可以使用PowerBI的內(nèi)置圖表來完成，就不再贅述。

關(guān)于商業(yè)圖表標(biāo)準(zhǔn)化，請參考我們的三篇雄文以及系統(tǒng)化課程《PowerBI & Excel商業(yè)圖表標(biāo)準(zhǔn)》。

用局部切換來增強(qiáng)信息密度

細(xì)心的伙伴會發(fā)現(xiàn)：

沒錯，這確實(shí)是一個按鈕，點(diǎn)擊后：

在圖表與數(shù)據(jù)之間切換，便于CEO在左腦和右腦之間切換。CEO夸張到：太貼心了，就像開著寶馬在運(yùn)動檔和手動檔的切換，真沒想到PowerBI可以這樣。

體驗(yàn)CEO駕駛的快感

綜合上述所有內(nèi)容，我們就可以得到最終的結(jié)果（動畫或視頻）：

CEO可以暢快地考察不同的人以及隨時跟蹤各種表現(xiàn)。

總結(jié)

從技術(shù)上看，本文涉及PowerBI及DAX的知識點(diǎn)包括：

  • 日期表
  • 日期區(qū)間
  • 日期區(qū)間（XTD）
  • CALCULATE靈活的取消篩選
  • 動態(tài)指標(biāo)KPI
  • 雙向篩選
  • 篩選器的作用范圍
  • IBCS & ZebraBI

基于這些技術(shù)知識點(diǎn)可以構(gòu)造以下的業(yè)務(wù)價值：

  • 靈活動態(tài)的歷史區(qū)間
  • 靈活動態(tài)的XTD區(qū)間
  • 動態(tài)的KPI
  • 任意的篩選以鎖定最近以及歷史表現(xiàn)以決策

本案例涉及到的系統(tǒng)化基礎(chǔ)課程：

《PowerBI自助商業(yè)智能分析基礎(chǔ)系列》

《PowerBI DAX基礎(chǔ)系列》

《PowerBI DAX實(shí)戰(zhàn)系列》

《PowerBI & Excel 商業(yè)圖表可視化標(biāo)準(zhǔn)》

總之，真正的簡潔是要基于堅實(shí)的基礎(chǔ)穿透復(fù)雜再形成的。世界上只有青藏高原而沒有電線桿。

本文案例文件已經(jīng)盡享至訂閱會員專區(qū)，祝您玩得愉快。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容