Power BI修正DAX公式 基于What If参数实现预测线与需求线相交

背景 #

开展预测相关实验过程中，Power BI自带的预测功能无法满足业务需求：

• 内置预测功能仅能基于历史生产率生成假设，预测未来12个月的月度平均收据开具量，未考虑特殊事件影响。
  
• 疫情期间封控、社交距离政策导致企业收据开具能力受限，开具量骤降，产生大量积压需求，该波动可在上图折线图中直观体现，内置预测未覆盖该部分逻辑。
• 自定义积压需求计算规则：取月度平均收据开具量，加上上月平均开具量与实际开具量的差值，得到当月预期积压需求；当月预期积压需求与下月实际开具量的差值，即为下月预期积压需求，按月迭代计算。
• 按上述规则测算的初始效果如下：
  
  图中各线含义：
  • 紫色实线：月度实际收据开具量（与第一张折线图的蓝线一致）
  • 黄色虚线：收据开具量预测值
  • 橙色虚线：截至当前日期的已测算积压需求
  • 紫色虚线：未来一年的预测积压需求

初始实现代码 #

各指标初始DAX公式如下：

// 黄色虚线：收据开具量预测
Receipts Issued Forecast = 
VAR Forecast =
CALCULATE(
    SUM(Estimate[Receipts issued]),
    DATEADD(dimDate[Date], -1, YEAR)
)*'Production Rate %'[Production Rate % Value]
RETURN
IF(
    YEAR(MAX(dimDate[Date]))>YEAR(TODAY()),
    Forecast,
    SUM(Estimate[Receipts issued])
)

// 紫色实线：实际收据开具量
Receipts Issued = Estimate[Receipts Issued]

// 紫色虚线初始版：预测积压需求
Projected Estimated Demand = IF(DAY(TODAY()) <> 1, CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] >= DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))), CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] >= TODAY())))

// 橙色虚线：已发生积压需求
Estimated Demand = CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] <= TODAY()))

// 排除2020-2022异常期的积压需求基础计算
Demand Estimate (Excl 2020-2022) = 
VAR DayInRow = CALCULATE(MAX([Start of Month]))
VAR tmpTbl =
FILTER(
       'Estimate'
       ,'Estimate'[Start of Month]<=DayInRow 
)
                    
VAR finalTable = 
    ADDCOLUMNS(
        tmpTbl
        ,"PPTsIssuedPreviousRow"
                ,VAR previouStartOfMonth = 
                    DATEADD('Estimate'[Start of Month],-1, Month)
                RETURN
                            CALCULATE(
                                    SUM('Estimate'[Receipts issued])
                                    ,ALL('Estimate')
                                    ,previouStartOfMonth 
                            )
        ,"multiplication"
                ,[AvgYearlyReceiptsIssued (Excl 2020-2022)]*[AvgPercentageOfReceiptsIssuedThisMonth (Excl 2020-2022)]
           )
VAR result = SUMX(finalTable,[multiplication]- [PPTsIssuedPreviousRow])   
RETURN
ROUND(result,2)

从测算数据可观察到明确趋势：2018年疫情前企业收据开具量高于平均值，积压需求呈负向趋势；之后积压需求开始累积，疫情期间出现大幅飙升。

待实现功能与现存问题 #

黄色虚线绑定What If参数滑块，用户可拖动滑块选择生产率，对预测值做乘积计算。功能设计目标为：支持用户拖动滑块调整生产率，找到收据开具生产率需要提升的百分比，逐步消化预测积压需求，通过两条线的交点匹配自身可接受的需求消化时间节点。
目前黄色线已可正常响应滑块调整，但紫色虚线无法正确关联滑块逻辑，未达到预期效果：滑块调高生产率带动黄色线上移时，紫色虚线需要从第一个预测月开始，随超额预测开具量反向递减。

尝试修改紫色虚线DAX公式如下：

Projected Estimated Demand = 
VAR Forecast2 = CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] >= TODAY())) - [Receipts Issued Forecast]
VAR DayInRow = CALCULATE(MAX([Start of Month]))
VAR __date = TODAY()
VAR __year = YEAR(__date)
VAR __day = DAY(__date)
VAR __month = MONTH(__date)
VAR todaysdate = DATE(__year,__month-1,__day)
VAR tmpTbl =
    FILTER(
           'Estimate'
           ,Estimate[Start of Month] >= todaysdate
    )
                        
VAR finalTable = 
        ADDCOLUMNS(
            tmpTbl
            ,"PPTsIssuedPreviousRow"
                    ,VAR previouStartOfMonth = 
                        DATEADD('Estimate'[Start of Month],-1, Month)
                    RETURN
                                CALCULATE(
                                        SUM('Estimate'[Receipts issued])
                                        ,ALL('Estimate')
                                        ,previouStartOfMonth 
                                )
            ,"multiplication"
                    ,[AvgYearlyReceiptsIssued (Excl 2020-2022)]*[AvgPercentageOfReceiptsIssuedThisMonth (Excl 2020-2022)]
            ,"ReceiptsForecast"
                    ,Estimate[Receipts Issued Forecast]
            ,"ReceiptsEstDemandPreviousRow"
                    ,VAR previouStartOfMonth = 
                        DATEADD('Estimate'[Start of Month],-1, Month)
                    RETURN
                                CALCULATE(
                                        SUM(Estimate[Demand Estimate (Excl 2020-2022)])
                                        ,ALL('Estimate')
                                        ,previouStartOfMonth 
                                )
               )
VAR result = SUMX(finalTable, [ReceiptsEstDemandPreviousRow]-[ReceiptsForecast])
VAR Forecast = ROUND(result,2)
RETURN

IF(
   SUM(Estimate[Receipts issued]) = BLANK(),
   Forecast,
   Estimate[Estimated Demand]
)

修改后紫色虚线波动幅度过大，迭代计算逻辑存在问题，效果如下：

已知橙色线月度增幅约为10万，黄色线最低开具值约为100万，按业务逻辑紫色线不应出现回弹，应保持逐月平稳下降趋势。现需修正紫色线对应DAX公式，实现滑块联动的预期效果。

内容的提问来源于stack exchange，提问作者Sweepster