首先來看一個(gè)按日期（到日期級(jí)別細(xì)度）來的KPI圖。

柱形圖

image

非常凌亂。

只選擇一年，則有：

image

折線圖

沒有辦法看出任何走勢(shì)。如果改為折線圖，則有：

image

可以看出這個(gè)擺動(dòng)幅度非常大。

移動(dòng)平均原理

如果將任何一個(gè)點(diǎn)的值都由此前的7個(gè)值平均得到，就是7日移動(dòng)平均了?？疾烊缦碌氖疽鈭D：

image

解釋移動(dòng)平均：

image

例如對(duì)于第X號(hào)日期，其移動(dòng)平均為[X-6,X]共7日的指標(biāo)值的算術(shù)平均。

其 DAX 公式如下：

KPI.RA.7 = // Rolling average
CALCULATE( 
    [KPI] , 
    DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -7 , DAY ) 
) / 7

于是就可以得到：

image

常見錯(cuò)誤

很快就可以發(fā)現(xiàn)這個(gè)圖其實(shí)存在問題，如果我們選擇最初的 2016 年，則有：

image

可以看出這里的線存在問題，第一日的點(diǎn)與線差距很大，由于第一天的前面沒有日期，因此不應(yīng)該再除以 7 ，而應(yīng)該只考慮有 KPI 讀數(shù)的日期。

正確實(shí)現(xiàn)

KPI.RA.7 = // Rolling average
AVERAGEX( 
    DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -7 , DAY ) , 
    [KPI]
)

效果如下：

image

可以看出，這時(shí)候?qū)τ陂_始日期階段和沒有KPI數(shù)值的日期都在 AVERAGEX 中被很好地處理了。

動(dòng)態(tài)參數(shù)化

不難想到，可以將剛剛的度量值進(jìn)行動(dòng)態(tài)化，得到：

KPI.RA.X = // Rolling average
AVERAGEX( 
    DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -[VarX.Value] , DAY ) ,     [KPI]
)

當(dāng)選出 30 天移動(dòng)平均，則有：

image

從移動(dòng)平均做預(yù)測(cè)

我們單獨(dú)來看移動(dòng)平均的曲線，如下：

image

使用 PowerBI 分析面板的預(yù)測(cè)特性，如下：

image

當(dāng)然這個(gè)預(yù)測(cè)是完全基于曲線進(jìn)行的數(shù)據(jù)擬合，具體細(xì)節(jié)我們就不展開了。

從實(shí)用性來說，是很困難的，因?yàn)槟銦o法解釋這個(gè)算法是如何工作的。

使用趨勢(shì)線

可以繼續(xù)在 Power BI 折線圖的分析面板找到趨勢(shì)線，并添加后如下：

image

很明顯，這個(gè)趨勢(shì)線更好地說明了一種趨勢(shì)。

一個(gè)很自然的問題就來了，這個(gè)線是否有方程，我們可以讓預(yù)測(cè)按趨勢(shì)線延長(zhǎng)嗎？

很可惜這個(gè)問題在 Power BI 中是不行的。

最小二乘法

我們發(fā)現(xiàn)趨勢(shì)線的計(jì)算在 Power BI 中其實(shí)是采用了最小二乘法，那么如果我們可以實(shí)現(xiàn)最小二乘法，我們就可以繪制這個(gè)趨勢(shì)線，進(jìn)而自行去延長(zhǎng)了。

下面來詳細(xì)說明最小二乘法的實(shí)現(xiàn)。

最小二乘法（英語：least squares method），又稱最小平方法，是一種數(shù)學(xué)優(yōu)化方法。它通過最小化誤差的平方和尋找和給定的數(shù)據(jù)點(diǎn)們的最佳匹配的函數(shù)曲線。

目的：利用最小二乘法可以簡(jiǎn)便地求得未知的數(shù)據(jù)，并使得這些求得的數(shù)據(jù)與實(shí)際數(shù)據(jù)之間誤差的平方和為最小。

最小二乘法通常歸功于高斯（Carl Friedrich Gauss，1795），但最小二乘法是由阿德里安-馬里·勒讓德（Adrien-Marie Legendre）首先發(fā)表的。

問題描述

image

某個(gè)實(shí)驗(yàn)得到四個(gè)紅色的點(diǎn)：(1,6),(2,5),(3,7),(4,10)，而我們知道這些點(diǎn)應(yīng)該與一條直線吻合，所以我們希望通過這幾個(gè)點(diǎn)來卡出一條直線，該直線與已知的數(shù)據(jù)點(diǎn)整體的差異最小。

數(shù)學(xué)模型

一般地，對(duì)于直線形的最簡(jiǎn)單的形式是：

image

我們需要確定 b₀ 和 b₁ 這兩個(gè)參數(shù)就可以鎖定這條直線。

[!NOTE]

感興趣的伙伴可以自行搜索關(guān)于最小二乘法的的求解過程以及更一般化內(nèi)容，這是一項(xiàng)非常重要的數(shù)學(xué)發(fā)現(xiàn)以及有很強(qiáng)的實(shí)用價(jià)值。它大致在高中和大學(xué)一年級(jí)時(shí)是標(biāo)準(zhǔn)的教學(xué)內(nèi)容。

這里直接給出 b₀ 和 b₁ 的解：

image

image

，為t值的算術(shù)平均值， 也可解得如下形式：

image

DAX 實(shí)現(xiàn)

假設(shè)隨著年份，銷售額呈現(xiàn)上升態(tài)勢(shì)，且存在這種線性的增長(zhǎng)趨勢(shì)，我們?nèi)绾瓮ㄟ^前4年的銷售額來推測(cè)未來3年的預(yù)測(cè)值就可以使用最小二乘法。

效果如下：

image

以及：

image

該問題的本質(zhì)度量值只需要一個(gè)，不妨稱為：KPI.Forecast.LeastSquaresMethod。

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

由于最小二乘法是用來做一個(gè)預(yù)測(cè)式計(jì)算的，因此，與實(shí)際業(yè)務(wù)數(shù)據(jù)模型有一定獨(dú)立性，故而考慮非侵入式設(shè)計(jì)。

建立一個(gè)計(jì)算表，承載 X 軸以及 AC/FC，同時(shí)將這個(gè)定制化的度量值放置在該表下面即可。

DAX實(shí)現(xiàn)

LeastSquaresMethod 表

LeastSquaresMethod = // 最小二乘法
SELECTCOLUMNS( 
    UNION(
        ADDCOLUMNS( 
            GENERATESERIES( 2016 , 2019 ) , -- AC 部分
            "Type" , "AC" 
        ),
        ADDCOLUMNS( 
            GENERATESERIES( 2020 , 2022 ) , -- FC 部分
            "Type" , "FC" 
        )
    ),
    "X" , [Value] , "Type" , [Type]
)

KPI.Forecast.LeastSquaresMethod 度量值

KPI.Forecast.LeastSquaresMethod = 
// 參數(shù)
// 參數(shù) - 1
//      該度量值依賴于：'LeastSquaresMethod'
//        'LeastSquaresMethod'[X]       - X 軸
//        'LeastSquaresMethod'[Type]    - AC or FC 區(qū)域，如果是 FC 區(qū)域則進(jìn)行擬合計(jì)算
// 參數(shù) - 2
//      [KPI] - 要計(jì)算的指標(biāo)，請(qǐng)?zhí)钊胂旅娴?2 位置
// 參數(shù) - 3
//      'Calendar'[日期] - 原模型中的表列，請(qǐng)?zhí)钊胂旅娴?3 位置

VAR X_Current = SELECTEDVALUE( 'LeastSquaresMethod'[X] )    -- 1
VAR AreaType = SELECTEDVALUE( 'LeastSquaresMethod'[Type] )  -- 1

VAR Y_Current = 
CALCULATE( 
    [KPI] , -- 2
    TREATAS ( { X_Current } , 'Calendar'[年份序號(hào)] ) -- 3
)

VAR Known =
    FILTER (
        SELECTCOLUMNS (
            CALCULATETABLE( VALUES( 'LeastSquaresMethod'[X] ) , 'LeastSquaresMethod'[Type] = "AC" , ALLSELECTED( 'LeastSquaresMethod' ) ),   -- 1
            "Known[X]", 'LeastSquaresMethod'[X],       -- 1
            "Known[Y]", CALCULATE( 
                            [KPI] , -- 2
                            TREATAS( 
                                { 'LeastSquaresMethod'[X] } ,  -- 1
                                'Calendar'[年份序號(hào)]   -- 3
                            ) 
                        )
        ),
        NOT ( ISBLANK ( Known[X] ) ) || NOT ( ISBLANK ( Known[Y] ) )
    )

// 以下無需修改

VAR Count_Items = COUNTROWS ( Known )
VAR Sum_X = SUMX ( Known, Known[X] )
VAR Sum_X2 = SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y = SUMX ( Known, Known[Y] )
VAR Sum_XY = SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X = AVERAGEX ( Known, Known[X] )
VAR Average_Y = AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept = Average_Y - Slope * Average_X
RETURN IF( AreaType = "AC" , Y_Current , Intercept + Slope * X_Current )

用于移動(dòng)平均

已經(jīng)解釋了移動(dòng)平均的做法以及可以用來做預(yù)測(cè)。 但問題是 PowerBI 給出的趨勢(shì)線不含有預(yù)測(cè)部分，如下：

image

如上圖所示，沒有辦法顯示趨勢(shì)線的延長(zhǎng)部分，我們使用自行實(shí)現(xiàn)的最小二乘法進(jìn)行修復(fù)如下：

image

可以看出，PowerBI 內(nèi)置的趨勢(shì)線的確是最小二乘法的實(shí)現(xiàn)，這與我們實(shí)現(xiàn)的最小二乘法完全吻合。

這樣一來，移動(dòng)平均就可以使用最小二乘法來進(jìn)行預(yù)測(cè)了。

總結(jié)

由于原始值受到各種隨機(jī)因素的影響，固然比較凌亂。我們進(jìn)行業(yè)務(wù)處理的套路是：

• 進(jìn)行移動(dòng)平均
• 進(jìn)行基于參數(shù)的動(dòng)態(tài)移動(dòng)平均
• 采用最小二乘法擬合出趨勢(shì)線
• 使用基于移動(dòng)平均和趨勢(shì)線的預(yù)測(cè)

[!NOTE]

由于直接使用度量值實(shí)現(xiàn)，這種基于移動(dòng)平均構(gòu)建的最小二乘法趨勢(shì)線也將保持動(dòng)態(tài)性。

因此，完美，絕對(duì)。