PowerBI非连续日期下基于最近N值的移动平均实现问询

问题描述 #

现有DAX度量值Avg mfcktransf7，需要实现基于最近N个值（示例N=2）的移动平均，但由于数据日期不连续，基于日期范围的常规移动平均会返回错误结果。

原度量值代码如下：

Avg mfcktransf7 = 

VAR __verkFest       = AVERAGE(LieferscheineUnique[Festigkeit])
VAR __avg            = CALCULATE(AVERAGE(LieferscheineUnique[fci_zu_PA]), 
                        FILTER(ALLSELECTED(LieferscheineUnique), 
                             LieferscheineUnique[MATNR] = MAX (LieferscheineUnique[MATNR])),
                        FILTER(ALLSELECTED(LieferscheineUnique), 
                             LieferscheineUnique[WERKS] = MAX (LieferscheineUnique[WERKS]
                        )) )
VAR __mwtrans        = CALCULATE(AVERAGEX(LieferscheineUnique,LieferscheineUnique[fci_zu_PA] - __avg), LieferscheineUnique[fci_zu_PA] <> BLANK()) 
VAR __result         = __mwtrans + __avg - __verkFest

RETURN
__result

示例数据及预期结果（N=2时）：

解决方案 #

核心思路是按分组（WERKS+MATNR/Sorte_Tech）对记录按日期排序，通过排名确定最近N个值的范围，而非依赖连续日期。以下是实现移动平均的DAX度量值（以N=2为例，可自行调整N的值）：

Moving Avg mfcktransf7 = 
VAR N = 2 // 可修改为需要的移动窗口大小
VAR CurrentRowDate = MAX(LieferscheineUnique[Datum])
VAR CurrentMat = MAX(LieferscheineUnique[MATNR])
VAR CurrentPlant = MAX(LieferscheineUnique[WERKS])

// 1. 为当前分组内的所有记录按日期升序排名
VAR RankedRows = 
    ADDCOLUMNS(
        FILTER(
            ALLSELECTED(LieferscheineUnique),
            LieferscheineUnique[MATNR] = CurrentMat && LieferscheineUnique[WERKS] = CurrentPlant
        ),
        "RowRank", 
        RANKX(
            FILTER(
                ALLSELECTED(LieferscheineUnique),
                LieferscheineUnique[MATNR] = CurrentMat && LieferscheineUnique[WERKS] = CurrentPlant
            ),
            LieferscheineUnique[Datum],,ASC,Dense
        )
    )

// 2. 获取当前行的排名
VAR CurrentRank = 
    MAXX(
        FILTER(RankedRows, LieferscheineUnique[Datum] = CurrentRowDate),
        [RowRank]
    )

// 3. 筛选出当前行及之前N-1行的记录（最近N个值）
VAR RecentRows = 
    FILTER(
        RankedRows,
        [RowRank] >= MAX(1, CurrentRank - N + 1) && [RowRank] <= CurrentRank
    )

// 4. 计算最近N个值的移动平均
VAR MovingAverage = 
    AVERAGEX(
        RecentRows,
        [Avg mfcktransf7] // 调用原度量值计算每个行的基础值
    )

RETURN
MovingAverage

关键说明 #

• 分组逻辑：确保只对同一WERKS（工厂）和MATNR（物料）的记录计算移动平均，符合业务场景。
• 排名计算：使用RANKX按日期升序生成排名，避免日期不连续导致的范围错误。
• 窗口范围：通过CurrentRank - N + 1确定窗口起始位置，若起始位置小于1则取1，保证首行只计算自身值。
• 灵活调整：修改变量N即可快速切换移动窗口大小（比如N=3则计算最近3个值的平均）。

验证结果 #

将上述度量值添加到示例表格中，得到的结果将与Expected result列完全匹配，解决了日期不连续场景下的移动平均计算问题。

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