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

  • 2019 年 10 月 6 日
  • 筆記

長期關注PowerBI戰友聯盟的戰友會發現，我們現在的很多文章出現了連載的跡象。我們在此前的文章以及系統化的影片教程中已經講解了PowerBI及DAX基礎部分，我們的文章將不斷基於這些基礎給出非常現實的設計。每篇文章可能會以及此前的文章，並重點解決某類痛點，最後給出一個綜合的標準實現。

當您下載到標準實現後，也許您直接看到這個設計是不容易理解的，因為它不是點一點出來了，是基於大量基礎和優化過的思想進行的，需要您結合對應文章提到的前序文章和實現來理解。本文繼續。

最終效果

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

我們簡單解讀一下：對於銷售額，這裡考察經理楚傑在相對於報表刷新日期前一天（設為：基準日）的業績表現；左邊是相對於基準日的MTD的表現；右邊是歷史不同月份的表現；非常清晰地看到了楚傑對銷售額完成的全景。

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

建議您在看下文時稍作停留，您可以觀察和思考下，上圖中可能涉及到的PowerBI或DAX的難點，如果您的思考都在後文里，說明您可以解構一個設計了，當然，能做到不看後文而完全解構的可能性太小了。

前情回顧

對於CEO查看各種指標，我們此前兩篇文章已經給出了不錯的開始，可以參考：

《PowerBI 零基礎支援上百指標多日期切換分析模板》以及《Excel CEO 終極駕駛艙 – 第一彈》。

在其中：

以及：

這很好。存在一個問題：

大部分 CEO 並不是在一個月（季度，周）結束時才關注目標的進展，他們需要隨時可以看到項目當前的進展。

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

再比如，預定的一個季度的銷售指標，當前的MTD需要與上月的MTD做及時的對比，而不是等到這個月結束再對比。

那麼，在如上的歷史分析圖表中就無法應對了。

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

PowerBI 的不足

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

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

我們會發現：PowerBI給出的日曆周是從周日開始的。這還不是最大的問題，最大的問題是這裡的相對日期並沒有TD（To Date）的概念，例如：WTD指的是本周一至今；MTD指的是本月一日至今。

因此，PowerBI的相對日期區間比較受限。為此，我們需要構建我們自己的相對日期架構。

超級靈活的相對日期架構XTD

除了不再重複 PowerBI 的相對日期外，我們給出一套更有意義的相對日期XTD架構設計。所謂XTD就是DTD，WTD，MTD，QTD，YTD。含義為：當日，當周1日至當日，當月1日至當日，當季1日至當日，當年1日至當日。

值得強調的是：當【X區間】的1日至當日 中的 「當」 字是非常精確的說法。不是本年至今，更不是本月至今。本年至今，從語義上看，是現實世界的時間；而我們所說的 當期的1日至當日 並不一定是現實世界的時間。如果您根本對此沒有感覺，那本文您將收穫頗豐；如果您完全明白我們的意思，說明您對日期智慧（準確講不是時間智慧，而是日期智慧）有了深入的思考和理解。

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

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

  • 相對於刷新日期還是現實日期。本例中，刷新日期：2019.07.14而現實日期是今日。
  • 截止到最後一日還是最後一日的上一日。在實際中，最後一日可能是數據不全的，因此可能需要上一日。
  • 區間類型是XTD。需要實現6種。
  • 同比上期。需要實現5種。

我們來看下DAX實現：

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 ) , "區間類型" , "當日" , "截止到" , "最後一日" , "區間類型排序" , 1 )      VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "區間類型" , "當日" , "截止到" , "上一日" , "區間類型排序" , 1 )      VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "區間類型" , "上一日" , "截止到" , "最後一日" , "區間類型排序" , 2 )      VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "區間類型" , "上一日" , "截止到" , "上一日" , "區間類型排序" , 2 )      VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "區間類型" , "WTD" , "截止到" , "最後一日" , "區間類型排序" , 3 )      VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "區間類型" , "WTD" , "截止到" , "上一日" , "區間類型排序" , 3 )      VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "區間類型" , "MTD" , "截止到" , "最後一日" , "區間類型排序" , 4 )      VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "區間類型" , "MTD" , "截止到" , "上一日" , "區間類型排序" , 4 )      VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "區間類型" , "QTD" , "截止到" , "最後一日" , "區間類型排序" , 5 )      VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "區間類型" , "QTD" , "截止到" , "上一日" , "區間類型排序" , 5 )      VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "區間類型" , "YTD" , "截止到" , "最後一日" , "區間類型排序" , 6 )      VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "區間類型" , "YTD" , "截止到" , "上一日" , "區間類型排序" , 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 ) , "區間類型" , "當日" , "截止到" , "最後一日" , "區間類型排序" , 1 )      VAR TodayPX = ADDCOLUMNS( LASTDATE( PX ) , "區間類型" , "當日" , "截止到" , "上一日" , "區間類型排序" , 1 )      VAR YesterdayX = ADDCOLUMNS( PREVIOUSDAY( X ) , "區間類型" , "上一日" , "截止到" , "最後一日" , "區間類型排序" , 2 )      VAR YesterdayPX = ADDCOLUMNS( PREVIOUSDAY( PX ) , "區間類型" , "上一日" , "截止到" , "上一日" , "區間類型排序" , 2 )      VAR WTDX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , X , - WEEKDAY( X , 2 ) + 1  , DAY ) , "區間類型" , "WTD" , "截止到" , "最後一日" , "區間類型排序" , 3 )      VAR WTDPX = ADDCOLUMNS( DATESINPERIOD( 'DatesTemplate'[Date] , PX , - WEEKDAY( PX , 2 ) + 1  , DAY ) , "區間類型" , "WTD" , "截止到" , "上一日" , "區間類型排序" , 3 )      VAR MTDX = ADDCOLUMNS( DATESMTD( X ) , "區間類型" , "MTD" , "截止到" , "最後一日" , "區間類型排序" , 4 )      VAR MTDPX = ADDCOLUMNS( DATESMTD( PX ) , "區間類型" , "MTD" , "截止到" , "上一日" , "區間類型排序" , 4 )      VAR QTDX = ADDCOLUMNS( DATESQTD( X ) , "區間類型" , "QTD" , "截止到" , "最後一日" , "區間類型排序" , 5 )      VAR QTDPX = ADDCOLUMNS( DATESQTD( PX ) , "區間類型" , "QTD" , "截止到" , "上一日" , "區間類型排序" , 5 )      VAR YTDX = ADDCOLUMNS( DATESYTD( X ) , "區間類型" , "YTD" , "截止到" , "最後一日" , "區間類型排序" , 6 )      VAR YTDPX = ADDCOLUMNS( DATESYTD( PX ) , "區間類型" , "YTD" , "截止到" , "上一日" , "區間類型排序" , 6 )      RETURN UNION( TodayX , TodayPX , YesterdayX , YesterdayPX , WTDX , WTDPX , MTDX , MTDPX , QTDX , QTDPX , YTDX , YTDPX )  RETURN      UNION(          ADDCOLUMNS( Period_Today , "相對日期" , "現實日期" ),          ADDCOLUMNS( Period_LastUpdate , "相對日期" , "刷新日期" )      )

這裡不做過多解釋，因為這裡的DAX並不複雜，僅僅用來拼裝這個結構。得到：

再來看看數據模型。

數據模型

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

對關鍵進行說明：

  • 【必】不直接創建日期表，而是創建一個日期表模板。（原因不展開，記住就行）
  • 日期表 = 日期表模板。
  • 日期區間表，並與日期表構建雙向篩選。
  • 日期區間表（XTD版），並與日期表構建雙向篩選。

值得強調的是（以下針對高手）：

  • 日期表模板是必須的，避免對日期表的循環依賴。

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

其實本案例的數據模型和日期模型並不複雜，只是要穿透這種複雜性，就需要超越這種複雜性去理解透徹日期表和篩選背後的本質，這塊內容太過技術，我們不再展開，最終形成的結構卻是簡單的。

處理複雜的日期篩選

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

KPI.PX =  VAR vPeriodType = SELECTEDVALUE( DatesPeriod[區間類型] )  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取消對兩個日期區間表的篩選，這裡涉及到對CALCULATE參數的精確理解，不再展開。但我們可以給出一個結論：使用CALCULATE可以構建任意複雜的篩選結構。這樣，我們就有了正確計算PX的度量值。

度量值

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

其實非常簡單：

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

對於 KPI.PX 已經給出，不再贅述。

PowerBI 的篩選控制

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

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

對於每個篩選器，我們都要精確設置其影響的範圍。

什麼鬼？沒見過？請系統化學習我們出品的《PowerBI商業智慧分析基礎系列》。

能做好的設計的前提是非常堅實的基礎。

至此，整個設計就OK了。

用IBCS進行標準化

這裡直接使用了滿足IBCS商業圖表標準的ZebraBI for PowerBI做標準化展現，如下：

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

關於商業圖表標準化，請參考我們的三篇雄文以及系統化課程《PowerBI & Excel商業圖表標準》。 參考： 真正的商業圖表可視化之道-佈道篇 真正的商業圖表可視化之道-實踐篇 真正的商業數據可視化之道-工具篇

用局部切換來增強資訊密度

細心的夥伴會發現：

沒錯，這確實是一個按鈕，點擊後：

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

體驗CEO駕駛的快感

綜合上述所有內容，我們就可以得到最終的結果（動畫或影片）：

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

總結

從技術上看，本文涉及PowerBI及DAX的知識點包括：

  • 日期表
  • 日期區間
  • 日期區間（XTD）
  • CALCULATE靈活的取消篩選
  • 動態指標KPI
  • 雙向篩選
  • 篩選器的作用範圍
  • IBCS & ZebraBI

基於這些技術知識點可以構造以下的業務價值：

  • 靈活動態的歷史區間
  • 靈活動態的XTD區間
  • 動態的KPI
  • 任意的篩選以鎖定最近以及歷史表現以決策

總之，真正的簡潔是要基於堅實的基礎穿透複雜再形成的。世界上只有青藏高原而沒有電線杆。

本文案例文件已經盡享至訂閱會員專區，祝您玩得愉快。

——

擴展閱讀：

真正的商業圖表可視化之道-佈道篇 真正的商業圖表可視化之道-實踐篇 真正的商業數據可視化之道-工具篇 PowerBI 零基礎支援上百指標多日期切換分析模板 Excel CEO 終極駕駛艙 – 第一彈 相關基礎課程：

《PowerBI自助商業智慧分析基礎系列》 《PowerBI DAX基礎系列》 《PowerBI DAX實戰系列》 《PowerBI & Excel 商業圖表可視化標準》

歡迎交流

VirMach 便宜 VPS

QNews

QNews