DAX萬能組合函數：VAR變數+CALCULATE()+FILTER(ALL())組合

02-08

做DAX時間智能函數的朋友？一個CALCULATE()萬能組合函數就夠了！|VAR+CALCULATE()+FILTER(ALL

2017-07-05 PivotModel劉凱 Excel商務智能PowerPivot

一個DAX組合公式，就可以

搞定移動平均？
搞定排名？
搞定累計合計帕累托分析？
搞定全部時間智能表格函數？

大道至簡，使用PivotModel劉凱老師的DAX萬能組合函數就夠了！

其構成非常簡單：

VAR變數+CALCULATE()+FILTER(ALL())

一、前言：萬能組合函數的應用場景舉例

通過"VAR變數+CALCULATE()+FILTER(ALL())組合"這一萬能組合函數，除了排名排序、累計合計帕累托分析以外，還可以很輕鬆地解決時間智能分析中的問題。

排名的場景中是這樣的：只是CALCULATE後面使用了COUNTA（計數）聚合

帕累托累計求和的場景下是這樣的，只是CALCULATE後面使用了SUM聚合

移動平均的場景下，典型的公式是這樣的，只是CALCULATE後面使用了AVERAGE聚合：

=VAR CurrentDay=股價[日期索引]

RETURN CALCULATE(AVERAGE(股價[收盤價]),
FILTER(ALL(股價),股價[日期索引]>CurrentDay-10 && 股價[日期索引]<=CurrentDay))

以上涉及的是數值大小，日期索引編號的情況，同樣適用於非連續時間的情況。

對於其他自帶日期表的時間智能函數，

"VAR變數+CALCULATE()+FILTER(ALL())組合" 同樣可以輕鬆搞定！

只是有些情況下 VAR變數都省了！

簡單的應用場景案例介紹結束，下面進入正文：

二: 本文內容: 萬能函數對 DAX時間智能函數的替代和應用

什麼是DAX時間智能函數
如何與CALCULATE配套使用
原理：FILTER(ALL)重置篩選上下文，用VAR變數嵌套上下文，CALCULATE函數進行上下文轉換，從而對統計期間/統計時點隨心所欲地調整。（劃重點）
從語法/參數角度，理解時間智能函數成長捷徑：用SPLY（上年同期）輕鬆上手；通過DATESINPERIOD函數掌握時間智能集大成。
萬能的組合：VAR變數+CALCULATE()+FILTER(ALL())移動平均滾動預測等等。

越來越多的財務運營分析師，開始發揮數據領導力，用數據驅動增長，並關注Excel商務智能PowerPivot。如果你對Excel中的數據透視表（PivotTable）、數據透視圖（PivotChart）熟悉的話，同樣可以利用數據模型（PivotModel）來輔助開展多維透視、交互展現。

今天就來聊聊PowerPivot中的時間智能函數，以及如何通過CALCULATE函數開展時間智能分析。那麼

哪些DAX函數屬於時間智能函數？

有些Excel商務智能PowerPivot分析師會混淆日期與時間函數和時間智能函數的區別。

普通的日期與時間DAX函數雖然也使用參數<date>,但顯然都是非常簡單的數據處理，可由PowerQuery輕鬆取代。

時間智能函數語法參數和含義如下：

其實要進行區分起來很簡單，按照計算模式，最明顯的一點在於:

(1)日期與時間函數：通常是應用於計算列的；

(2) 時間智能函數:在開展時間智能函數處理時，分為兩類：

16個時間智能日期表函數。這裡給出語法參數和應用場景。全部都使用了<dates>參數。

使用FILTER(ALL),或者FILTER也可應對許多時間智能的場景，原理類似。

日期表格函數，自帶表格計算和重置上下文的屬性。並且能夠被

」VAR變數+CALCULATE()+FILTER(ALL())組合「替代。

那麼什麼是表格計算？而其與篩選上下文/行上下文又有什麼關係？

以下是典型的表格操控函數、與計值上下文相關的函數。

先來兩張圖，看看普通的表格函數是如何操控計值上下文的。

上圖中的「VAR變數函數」，同樣可以替代下圖中的鏈接/嵌套上下文函數EARLIER的用法。

主要的區別在於視角：微觀的表格行列處理 vs 直觀的統計期間篩選條件。

當談到對上下文的操控時，確實不容易理解。因此我們把範圍縮小到時間智能場景下。可以簡單地理解為：

篩選上下文：就是個統計期間；默認的統計期間通常就是自然年月
重置上下文：就是從一個統計期間，調整為另一個統計期間。比如當期發生額變為累計額、上期發生額、上年同期發生額等等。。。。
行上下文：就是個統計時點；默認的統計時點就是某一天。
嵌套上下文：就是比較下同一個欄位中，時間的早晚（或者用於數值的大小）的比對。

時間趨勢分析的主要任務，是進行統計期間的調整（同比、環比、基準比、累計值等等），需要對篩選上下文進行重置；

時間趨勢的另一類任務，是將某個統計時點變為一個統計時間段（如移動平均）。

當進行時間早晚的對比時，我們最好的辦法是創建一個分身。通常有三種辦法來實現：

（1）Power Query中的List.Buffer，可以直接在PQ中開展ABC分析。

（2）VAR變數。

（3）EARLIER函數

使用PQ中的List.Buffer效率遠高於EARLIER()函數，但自從EXCEL 2016之後，基本上也可使用VAR變數代替EARLIER。

這實際上為我們打開DAX應用的一門鑰匙。因為在PivotModel劉凱老師看來，掌握PowerPivot各個要素，其重要性次序為：。

數據模型＞上下文操控 > DAX函數。

如果熟練正確地建立數據模型，並靈活操控計值上下文，那麼只需很簡單的DAX就能解決複雜的分析問題。一個簡單的例子就是 新增客戶的N期留存率指標（同期群分析）創建。通過正確的數據模型欄位設置+上下文操控+DISTINCTCOUNT就可以搞定。

3：時間智能日期表函數的本質原理

那麼回到時間智能函數，其中絕大多數的是遵從表格計算的日期表函數，並且遵循「重置上下文」的模式，也就是說，用一個新的統計期間，代替原來默認的統計期間。

時間智能日期表函數的語法很簡單：

CALCULATE()+<高級篩選器>，

這16個日期表函數就是高級篩選器而已

也就是說：

CALCULATE()+ FILTER(ALL)函數，重置了篩選上下文，也就是採用新的統計期間。CALCULATE()+ FILTER(ALL)函數完全可以替代上述的16個日期表函數，而且不僅僅處理自然年/月，還能處理另類的日期數據分組，如零售業的4-4-5日曆。。

值得注意的是，雖然FILTER是個表格函數，但FILTER(ALL)的作用則是重置篩選上下文。如果你一開始就用表格行列思維方式來開展思考的話，很容易就陷入到細節當中，非常不利於開展設計。

正確的做法是：站在統計期間、統計時點的角度考慮問題。通常統計期間就是篩選上下文，某個日期通常是個當前行上下文。

4：掌握時間智能函數的捷徑

SPLY(上年同期)快速上手。

讓我們回歸到時間智能日期表函數。雖然乍一看16個函數有些多，但是用法都比較相似。每個函數的使用場景、語法參數也略有不同而已。
先來看看參數多少。其中SPLY(上年同期)的參數最少，僅一個；DATESINPERIOD函數的參數最多，多達4個。
也就是說，在掌握時間智能函數時，SPLY是最簡單的出發點.，從上年同期出發，可以快速掌握DATEADD、上年YTD、平行期多個時間智能函數類型。

圖：從時間智能函數中的SPLY上手，就對了！

（由於時間智能函數，課程中經常講到，因此頻繁習慣使用縮略詞）

DATESINPERIOD包含的參數最多，級別涵蓋了其他時間智能函數的參數，是快速掌握時間之恩函數的捷徑，一通百通。

5.DATESINPERIOD集合時間智能大成。

最綜合的時間智能函數：DATESINPERIOD函數。

DATESINPERIOD函數非常經典，在學習時間智能函數時可謂一通百通。在《可視化分析必備１８招》中，移動平均位列前十名，其重要程度可見一斑。

掌握一個CALCULATE函數+DATESINPERIOD函數的分析練習後，可以有效串聯整個時間智能函數的精髓：

（１）日期表。設置時間表，並標記日期列。對應於第一個參數date

（２）期初

（３）期末

（４）期間：時間間隔的期間+期數：Ｎ個年／月／周／日。

要玩轉時間智能函數，無非就是語法參數和時間的關係。

如何選擇不同的時間智能函數：

6.時間智能的應用場景。

（１）如果只是簡單的按照自然年月地進行分組分析。統計期間非常大眾化。只要自定義一個日期表函數就可以了。

（2）如果需要是非常個性化的時間分組，比如零售業，通常自定義一個4-4-5日曆表（默認自帶的不可），這就需要我們使用CALCULATE+FILTER(ALL)函數的組合

（3）具體分析工作中，期初、期末、統計期間長度、間隔和期數都會發生變化。需要使用16個時間智能函數，當然CALCULATE+FILTER(ALL)函數

（4）如果需要從某個統計時點（某天），變為　新的統計期間。那麼就需要

VAR變數+CALCULATE+FILTER(ALL)函數。

（小BUG:當然目前VAR在定義變數時，變數名稱還是需要英文）

關鍵在於對統計期間／統計時點的調整。

7. 掌握時間智能函數的主線。

掌握時間智能函數的主線：關注統計期間和時間點是如何進行調整，並採取相應的時間智能函數。

注意：雖然PivotModel劉凱老師的綠皮書《Excel２０１３：用ＰｏｗｅｒＰｉｖｏｔ創建數據模型》一書中並未提到DATESINPERIOD函數，但是視頻課程中詳細講解了該函數並給出具體的應用。

按照從易到難的次序：

上一期／下一期：統計期間的時長－不變。移動的距離為＋１或者－１期

期初至今／上年同期：統計期間的時長變了，或者統計期間發生了不規則移動，如１年。

全新的統計期間：統計期間的時長不變，但間隔變化了；或者期間時長變了。期間的間隔／數量，期初／末的時點都可以進行自定義。

以DATESINPERIOD函數為例，很容易用於滾動預測，並選擇不同的歷史數據長度來平滑曲線，生成預測線和預測期間. 只有地調整期間數可以大大提升預測精度：

是不是非常輕鬆？就將16個時間智能函數直接掌握？區別就在參數和實現的功能而已。

當然，重點來了。DAX中的萬能鑰匙！！！

8 CALCULATE()+FILTER(ALL())用於年初至今計算，代替YTD：

在綠皮書和PivotModel劉凱老師的課程中已經詳細解讀了。對於其他時間智能函數而言，存在許多共性之處。

然而，VAR變數+CALCULATE()+FILTER(ALL())在此基礎之上更近了一步。

VAR變數具有比EARLIER更靈活直觀的嵌套上下文能力。只要數據模型搭建合理，完全可以將重置上下文和嵌套上下文結合起來，把數據玩弄於股掌之中。例如，可以非常靈活地開展ABC分析，80/20分析，創建互動式帕累托圖。

對於不連續的數據，我們仍舊可以用DAX中的這個萬能鑰匙/瑞士軍刀輕鬆解鎖非連續的股票數據。下圖是PivotModel劉凱老師綠皮書中的微軟/蘋果的股價數據。

N日（以50天）移動平均為例，要處理的要素列舉如下：

本質上就是我們所說的將統計時點變為統計期間，可以分解為三個步驟：

其中重點在於第二步，可以用統計期間篩選視角，也可以用物理表格的視角。

通過計算列，可以用三種不同的移動平均方法來實現：

（1）10日移動平均：VAR變數+CALCULATE()+FILTER(ALL())函數法

（2）50日移動平均：EARLIER函數()+CALCULATE()+FILTER()法

（3）90日移動平均：EARLIER函數()+AVERAGEX法

其中各個公式中的第二步的同時突出顯示如下：

其實無論10天、50天、90天，僅使用VAR變數+CALCULATE()+FILTER(ALL())都可以完成，這裡是比較不同的計算移動平均的方法。要轉換不同的N天移動平均曲線，只需要輕鬆地將三個欄位簡單拖拽即可。

9.為何推薦VAR變數+CALCULATE()+FILTER(ALL())函數法

原因很簡單：

（1）大道至簡，用一個DAX組合函數解決各種分析問題，區別僅在於個別聚合方式或者參數上。

（2）語法結構非常清晰，符合認知邏輯。

FILTER(ALL())是用來重置統計期間的（篩選上下文），這個做法我們非常熟悉，可以輕鬆取代本文的16個時間智能函數；

VAR變數是用來嵌套兩個不同的上下文的，也就是說兩個相同的統計時點，一動，另一個不動。這裡VAR在命名時暫時智能用英文，我們命名為CurrentDay，臨時保持相對不動，而股價[日期索引]為動態的。

（3）性能處理速度更快。EARLIER()通常計算緩慢，而使用PQ中的List.Buffer，或者VAR是提升處理性能的更加選擇。

10.總結:時間智能的要點。

時間智能的日期表函數，主要都是通過以<dates>為主的語法參數，來調整日期有關的篩選條件，實現重置統計期間範圍的目的。例如用累計至今代替當前月份。
時間智能函數都是和CALCULATE函數配合使用的。因此要一網打盡，重點在於區分不同的功能和參數，理解FILTER(ALL)函數的原理
在時間智能函數種中，DATESINPERIOD函數的參數最多，對統計期間的調整最靈活。雖然綠皮書中沒有收錄，但PivotModel劉凱老師的在移動平均、滾動預測中都給出該函數的高級玩法。學習DATESINPERIOD函數是掌握時間智能函數的捷徑，一通百通。
對於這裡的16個時間智能函數，完全可以用CALCULATE()函數+ FILTER(ALL)函數組合的方式，來代替CALCULATE()函數+時間智能函數的方式，其重置篩選上下文的本質都是一樣的。
CALCULATE()函數+ FILTER(ALL)函數+VAR變數的組合，是DAX函數中的萬能鑰匙，可以擺脫過時的EARLIER函數的束縛，輕鬆解鎖許多複雜的分析模式，如ABC分析、80/20分析、N日移動平均等等等等。關鍵在於正確的數據模型和靈活的計值上下文操控，來實現重置上下文嵌套上下文。