PowerBI 基于移动平均及最小二乘法的动态趋势预测

BI佐罗

发布于 2019-12-04 11:32:44

2.5K0

发布于 2019-12-04 11:32:44

文章被收录于专栏：PowerBI战友联盟

首先来看一个按日期（到日期级别细度）来的KPI图。

柱形图

非常凌乱。

只选择一年，则有：

折线图

没有办法看出任何走势。如果改为折线图，则有：

可以看出这个摆动幅度非常大。

移动平均原理

如果将任何一个点的值都由此前的7个值平均得到，就是7日移动平均了。考察如下的示意图：

解释移动平均：

例如对于第X号日期，其移动平均为[X-6,X]共7日的指标值的算术平均。

其 DAX 公式如下：

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

于是就可以得到：

常见错误

很快就可以发现这个图其实存在问题，如果我们选择最初的 2016 年，则有：

可以看出这里的线存在问题，第一日的点与线差距很大，由于第一天的前面没有日期，因此不应该再除以 7 ，而应该只考虑有 KPI 读数的日期。

正确实现

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

效果如下：

可以看出，这时候对于开始日期阶段和没有KPI数值的日期都在 AVERAGEX 中被很好地处理了。

动态参数化

不难想到，可以将刚刚的度量值进行动态化，得到：

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

当选出 30 天移动平均，则有：

从移动平均做预测

我们单独来看移动平均的曲线，如下：

使用 PowerBI 分析面板的预测特性，如下：

当然这个预测是完全基于曲线进行的数据拟合，具体细节我们就不展开了。

从实用性来说，是很困难的，因为你无法解释这个算法是如何工作的。

使用趋势线

可以继续在 Power BI 折线图的分析面板找到趋势线，并添加后如下：

很明显，这个趋势线更好地说明了一种趋势。

一个很自然的问题就来了，这个线是否有方程，我们可以让预测按趋势线延长吗？

很可惜这个问题在 Power BI 中是不行的。

最小二乘法

我们发现趋势线的计算在 Power BI 中其实是采用了最小二乘法，那么如果我们可以实现最小二乘法，我们就可以绘制这个趋势线，进而自行去延长了。

下面来详细说明最小二乘法的实现。

最小二乘法（英语：least squares method），又称最小平方法，是一种数学优化方法。它通过最小化误差的平方和寻找和给定的数据点们的最佳匹配的函数曲线。

目的：利用最小二乘法可以简便地求得未知的数据，并使得这些求得的数据与实际数据之间误差的平方和为最小。

最小二乘法通常归功于高斯（Carl Friedrich Gauss，1795），但最小二乘法是由阿德里安-马里·勒让德（Adrien-Marie Legendre）首先发表的。

问题描述

某个实验得到四个红色的点：(1,6),(2,5),(3,7),(4,10)，而我们知道这些点应该与一条直线吻合，所以我们希望通过这几个点来卡出一条直线，该直线与已知的数据点整体的差异最小。

数学模型

一般地，对于直线形的最简单的形式是：

我们需要确定 b0 和 b1 这两个参数就可以锁定这条直线。

感兴趣的伙伴可以自行搜索关于最小二乘法的的求解过程以及更一般化内容，这是一项非常重要的数学发现以及有很强的实用价值。它大致在高中和大学一年级时是标准的教学内容。

这里直接给出 b0 和 b1 的解：

其中，

，为t值的算术平均值，也可解得如下形式：

DAX 实现

假设随着年份，销售额呈现上升态势，且存在这种线性的增长趋势，我们如何通过前4年的销售额来推测未来3年的预测值就可以使用最小二乘法。

效果如下：

该问题的本质度量值只需要一个，不妨称为：KPI.Forecast.LeastSquaresMethod。

给出其 DAX 实现如下：

KPI.Forecast.LeastSquaresMethod =

// 初始化设置
// 参数 1：原列区间列引用，如：'Calendar'[年份序号]
// 参数 2：新列区间列引用，如：Year2016to2022[Year]

VAR X_Current = SELECTEDVALUE( Year2016to2022[Year] )   -- 1

VAR Y_Current =
CALCULATE(
    [KPI] ,
    TREATAS( { X_Current } , 'Calendar'[年份序号] ) -- 2
)

VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( Year2016to2022[Year] ),   -- 1
            "Known[X]", Year2016to2022[Year],       -- 1
            "Known[Y]", CALCULATE(
                            [KPI] ,
                            TREATAS(
                                { Year2016to2022[Year] } , -- 1
                                'Calendar'[年份序号]        -- 2
                            )
                        )
        ),
        AND (
            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( ISBLANK( Y_Current ) , Intercept + Slope * X_Current )

为了让结果呈现分段效果，再构建一个用于图例的表，其 DAX 实现如下：

Legend.AC/FC =
SELECTCOLUMNS( { "AC" , "FC" } , "AC/FC" , [Value] )

以及用来进行显示的度量值，不妨称为 KPI.Display ，其 DAX 实现如下：

KPI.Display =
VAR Y =
    CALCULATE(
        [KPI] ,
        TREATAS( { SELECTEDVALUE( Year2016to2022[Year] ) } , 'Calendar'[年份序号] )
    )
VAR Legend = SELECTEDVALUE( 'Legend.AC/FC'[AC/FC] )
RETURN SWITCH( TRUE() ,
    Legend = "AC" , Y ,
    Legend = "FC" && ISBLANK( Y ) , [KPI.Forecast.LeastSquaresMethod]
)

这里又一次使用了：动态挂载的非侵入式设计 以及 DAX 驱动可视化 的思想。