首先來看一個按日期（到日期級別細(xì)度）來的KPI圖。
柱形圖
非常凌亂。
只選擇一年，則有：
折線圖
沒有辦法看出任何走勢。如果改為折線圖，則有：
可以看出這個擺動幅度非常大。
移動平均原理
如果將任何一個點(diǎn)的值都由此前的7個值平均得到，就是7日移動平均了?？疾烊缦碌氖疽鈭D：
解釋移動平均：
例如對于第X號日期，其移動平均為[X-6,X]共7日的指標(biāo)值的算術(shù)平均。
其 DAX 公式如下：
KPI.RA.7 = // Rolling average
CALCULATE(
[KPI] ,
DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -7 , DAY )
) / 7
于是就可以得到：
常見錯誤
很快就可以發(fā)現(xiàn)這個圖其實(shí)存在問題，如果我們選擇最初的 2016 年，則有：
可以看出這里的線存在問題，第一日的點(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]
)
效果如下：
可以看出，這時候?qū)τ陂_始日期階段和沒有KPI數(shù)值的日期都在 AVERAGEX 中被很好地處理了。
動態(tài)參數(shù)化
不難想到，可以將剛剛的度量值進(jìn)行動態(tài)化，得到：
KPI.RA.X = // Rolling average
AVERAGEX(
DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -[VarX.Value] , DAY ) , [KPI]
)
當(dāng)選出 30 天移動平均，則有：
從移動平均做預(yù)測
我們單獨(dú)來看移動平均的曲線，如下：
使用 PowerBI 分析面板的預(yù)測特性，如下：
當(dāng)然這個預(yù)測是完全基于曲線進(jìn)行的數(shù)據(jù)擬合，具體細(xì)節(jié)我們就不展開了。
從實(shí)用性來說，是很困難的，因?yàn)槟銦o法解釋這個算法是如何工作的。
使用趨勢線
可以繼續(xù)在 Power BI 折線圖的分析面板找到趨勢線，并添加后如下：
很明顯，這個趨勢線更好地說明了一種趨勢。
一個很自然的問題就來了，這個線是否有方程，我們可以讓預(yù)測按趨勢線延長嗎？
很可惜這個問題在 Power BI 中是不行的。
最小二乘法
我們發(fā)現(xiàn)趨勢線的計(jì)算在 Power BI 中其實(shí)是采用了最小二乘法，那么如果我們可以實(shí)現(xiàn)最小二乘法，我們就可以繪制這個趨勢線，進(jìn)而自行去延長了。
下面來詳細(xì)說明最小二乘法的實(shí)現(xiàn)。
最小二乘法（英語：least squares method），又稱最小平方法，是一種數(shù)學(xué)優(yōu)化方法。它通過最小化誤差的平方和尋找和給定的數(shù)據(jù)點(diǎn)們的最佳匹配的函數(shù)曲線。
目的：利用最小二乘法可以簡便地求得未知的數(shù)據(jù)，并使得這些求得的數(shù)據(jù)與實(shí)際數(shù)據(jù)之間誤差的平方和為最小。
最小二乘法通常歸功于高斯（Carl Friedrich Gauss，1795），但最小二乘法是由阿德里安-馬里·勒讓德（Adrien-Marie Legendre）首先發(fā)表的。
問題描述
某個實(shí)驗(yàn)得到四個紅色的點(diǎn)：(1,6),(2,5),(3,7),(4,10)，而我們知道這些點(diǎn)應(yīng)該與一條直線吻合，所以我們希望通過這幾個點(diǎn)來卡出一條直線，該直線與已知的數(shù)據(jù)點(diǎn)整體的差異最小。
數(shù)學(xué)模型
一般地，對于直線形的最簡單的形式是：
我們需要確定 b0 和 b1 這兩個參數(shù)就可以鎖定這條直線。
[!NOTE]
感興趣的伙伴可以自行搜索關(guān)于最小二乘法的的求解過程以及更一般化內(nèi)容，這是一項(xiàng)非常重要的數(shù)學(xué)發(fā)現(xiàn)以及有很強(qiáng)的實(shí)用價(jià)值。它大致在高中和大學(xué)一年級時是標(biāo)準(zhǔn)的教學(xué)內(nèi)容。
這里直接給出 b0 和 b1 的解：
，為t值的算術(shù)平均值， 也可解得如下形式：
DAX 實(shí)現(xiàn)
假設(shè)隨著年份，銷售額呈現(xiàn)上升態(tài)勢，且存在這種線性的增長趨勢，我們?nèi)绾瓮ㄟ^前4年的銷售額來推測未來3年的預(yù)測值就可以使用最小二乘法。
效果如下：
以及：
該問題的本質(zhì)度量值只需要一個，不妨稱為：KPI.Forecast.LeastSquaresMethod。
數(shù)據(jù)模型
由于最小二乘法是用來做一個預(yù)測式計(jì)算的，因此，與實(shí)際業(yè)務(wù)數(shù)據(jù)模型有一定獨(dú)立性，故而考慮非侵入式設(shè)計(jì)。
建立一個計(jì)算表，承載 X 軸以及 AC/FC，同時將這個定制化的度量值放置在該表下面即可。
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)，請?zhí)钊胂旅娴?2 位置
// 參數(shù) - 3
// 'Calendar'[日期] - 原模型中的表列，請?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'[年份序號] ) -- 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'[年份序號] -- 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 )
用于移動平均
已經(jīng)解釋了移動平均的做法以及可以用來做預(yù)測。 但問題是 PowerBI 給出的趨勢線不含有預(yù)測部分，如下：
如上圖所示，沒有辦法顯示趨勢線的延長部分，我們使用自行實(shí)現(xiàn)的最小二乘法進(jìn)行修復(fù)如下：
可以看出，PowerBI 內(nèi)置的趨勢線的確是最小二乘法的實(shí)現(xiàn)，這與我們實(shí)現(xiàn)的最小二乘法完全吻合。
這樣一來，移動平均就可以使用最小二乘法來進(jìn)行預(yù)測了。
總結(jié)
由于原始值受到各種隨機(jī)因素的影響，固然比較凌亂。我們進(jìn)行業(yè)務(wù)處理的套路是：
- 進(jìn)行移動平均
- 進(jìn)行基于參數(shù)的動態(tài)移動平均
- 采用最小二乘法擬合出趨勢線
- 使用基于移動平均和趨勢線的預(yù)測
[!NOTE]
由于直接使用度量值實(shí)現(xiàn)，這種基于移動平均構(gòu)建的最小二乘法趨勢線也將保持動態(tài)性。
因此，完美，絕對。
