首先来看一个按日期（到日期级别细度）来的KPI图。
柱形图
image
非常凌乱。
只选择一年，则有：
image
折线图
没有办法看出任何走势。如果改为折线图，则有：
image
可以看出这个摆动幅度非常大。
移动平均原理
如果将任何一个点的值都由此前的7个值平均得到，就是7日移动平均了。考察如下的示意图：
image
解释移动平均：
image
例如对于第X号日期，其移动平均为[X-6,X]共7日的指标值的算术平均。
其 DAX 公式如下：
KPI.RA.7 = // Rolling average
CALCULATE(
[KPI] ,
DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -7 , DAY )
) / 7
于是就可以得到：
image
常见错误
很快就可以发现这个图其实存在问题，如果我们选择最初的 2016 年，则有：
image
可以看出这里的线存在问题，第一日的点与线差距很大，由于第一天的前面没有日期，因此不应该再除以 7 ，而应该只考虑有 KPI 读数的日期。
正确实现
KPI.RA.7 = // Rolling average
AVERAGEX(
DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -7 , DAY ) ,
[KPI]
)
效果如下：
image
可以看出，这时候对于开始日期阶段和没有KPI数值的日期都在 AVERAGEX 中被很好地处理了。
动态参数化
不难想到，可以将刚刚的度量值进行动态化，得到：
KPI.RA.X = // Rolling average
AVERAGEX(
DATESINPERIOD( 'Calendar'[日期] , MAX( 'Calendar'[日期] ) , -[VarX.Value] , DAY ) , [KPI]
)
当选出 30 天移动平均，则有：
image
从移动平均做预测
我们单独来看移动平均的曲线，如下：
image
使用 PowerBI 分析面板的预测特性，如下：
image
当然这个预测是完全基于曲线进行的数据拟合，具体细节我们就不展开了。
从实用性来说，是很困难的，因为你无法解释这个算法是如何工作的。
使用趋势线
可以继续在 Power BI 折线图的分析面板找到趋势线，并添加后如下：
image
很明显，这个趋势线更好地说明了一种趋势。
一个很自然的问题就来了，这个线是否有方程，我们可以让预测按趋势线延长吗？
很可惜这个问题在 Power BI 中是不行的。
最小二乘法
我们发现趋势线的计算在 Power BI 中其实是采用了最小二乘法，那么如果我们可以实现最小二乘法，我们就可以绘制这个趋势线，进而自行去延长了。
下面来详细说明最小二乘法的实现。
最小二乘法（英语：least squares method），又称最小平方法，是一种数学优化方法。它通过最小化误差的平方和寻找和给定的数据点们的最佳匹配的函数曲线。
目的：利用最小二乘法可以简便地求得未知的数据，并使得这些求得的数据与实际数据之间误差的平方和为最小。
最小二乘法通常归功于高斯（Carl Friedrich Gauss，1795），但最小二乘法是由阿德里安-马里·勒让德（Adrien-Marie Legendre）首先发表的。
问题描述
image
某个实验得到四个红色的点：(1,6),(2,5),(3,7),(4,10)，而我们知道这些点应该与一条直线吻合，所以我们希望通过这几个点来卡出一条直线，该直线与已知的数据点整体的差异最小。
数学模型
一般地，对于直线形的最简单的形式是：
image
我们需要确定 b0 和 b1 这两个参数就可以锁定这条直线。
[!NOTE]
感兴趣的伙伴可以自行搜索关于最小二乘法的的求解过程以及更一般化内容，这是一项非常重要的数学发现以及有很强的实用价值。它大致在高中和大学一年级时是标准的教学内容。
这里直接给出 b0 和 b1 的解：
image
其中，
image
，为t值的算术平均值， 也可解得如下形式：
image
DAX 实现
假设随着年份，销售额呈现上升态势，且存在这种线性的增长趋势，我们如何通过前4年的销售额来推测未来3年的预测值就可以使用最小二乘法。
效果如下：
image
以及：
image
该问题的本质度量值只需要一个，不妨称为：KPI.Forecast.LeastSquaresMethod。
数据模型
由于最小二乘法是用来做一个预测式计算的，因此，与实际业务数据模型有一定独立性，故而考虑非侵入式设计。
建立一个计算表，承载 X 轴以及 AC/FC，同时将这个定制化的度量值放置在该表下面即可。
DAX实现
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 =
// 参数
// 参数 - 1
// 该度量值依赖于：'LeastSquaresMethod'
// 'LeastSquaresMethod'[X] - X 轴
// 'LeastSquaresMethod'[Type] - AC or FC 区域，如果是 FC 区域则进行拟合计算
// 参数 - 2
// [KPI] - 要计算的指标，请填入下面的 2 位置
// 参数 - 3
// 'Calendar'[日期] - 原模型中的表列，请填入下面的 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 )
用于移动平均
已经解释了移动平均的做法以及可以用来做预测。 但问题是 PowerBI 给出的趋势线不含有预测部分，如下：
image
如上图所示，没有办法显示趋势线的延长部分，我们使用自行实现的最小二乘法进行修复如下：
image
可以看出，PowerBI 内置的趋势线的确是最小二乘法的实现，这与我们实现的最小二乘法完全吻合。
这样一来，移动平均就可以使用最小二乘法来进行预测了。
总结
由于原始值受到各种随机因素的影响，固然比较凌乱。我们进行业务处理的套路是：
- 进行移动平均
- 进行基于参数的动态移动平均
- 采用最小二乘法拟合出趋势线
- 使用基于移动平均和趋势线的预测
[!NOTE]
由于直接使用度量值实现，这种基于移动平均构建的最小二乘法趋势线也将保持动态性。
因此，完美，绝对。
网友评论