如何在PowerBI中用DAX计算过去4周（不含当前周）移动平均？

首先需要确保你有一个日期表，并与POS数据表建立关系，日期表需包含统一规则的周编号（如ISO周）和年份列，避免跨年周统计错误。以下是针对两种常见需求的DAX度量值：

需求1：过去4周POS总额的平均值 #

适用于需要将4周的总POS金额除以4得到平均的场景：

POS for P4W = 
VAR CurrentWeek = MAX('Date'[ISO Week Number])
VAR CurrentYear = MAX('Date'[Year])
-- 处理跨年周场景：当前周为年初前4周时，包含上一年的对应周
VAR PreviousWeeks = 
    IF(
        CurrentWeek <= 4,
        UNION(
            SELECTCOLUMNS(
                FILTER(ALL('Date'), 'Date'[Year] = CurrentYear - 1 && 'Date'[ISO Week Number] >= 53 - (4 - CurrentWeek)),
                "Year", 'Date'[Year],
                "Week", 'Date'[ISO Week Number]
            ),
            SELECTCOLUMNS(
                FILTER(ALL('Date'), 'Date'[Year] = CurrentYear && 'Date'[ISO Week Number] < CurrentWeek),
                "Year", 'Date'[Year],
                "Week", 'Date'[ISO Week Number]
            )
        ),
        SELECTCOLUMNS(
            FILTER(ALL('Date'), 'Date'[Year] = CurrentYear && 'Date'[ISO Week Number] >= CurrentWeek - 4 && 'Date'[ISO Week Number] < CurrentWeek),
            "Year", 'Date'[Year],
            "Week", 'Date'[ISO Week Number]
        )
    )
VAR TotalP4W = 
    CALCULATE(
        SUM('POS Data'[POS Amount]),
        TREATAS(PreviousWeeks, 'Date'[Year], 'Date'[ISO Week Number]),
        ALLSELECTED('POS Data'[Product ID]) -- 保留产品粒度（单品/品类等）的筛选上下文
    )
RETURN
    DIVIDE(TotalP4W, 4, 0) -- DIVIDE避免除以0错误，默认返回0

需求2：过去4周每周POS的平均值 #

适用于需要先计算每周的POS总额，再取这4周平均值的场景：

POS for P4W (周度平均) = 
VAR CurrentWeek = MAX('Date'[ISO Week Number])
VAR CurrentYear = MAX('Date'[Year])
VAR PreviousWeeks = 
    IF(
        CurrentWeek <= 4,
        UNION(
            SELECTCOLUMNS(
                FILTER(ALL('Date'), 'Date'[Year] = CurrentYear - 1 && 'Date'[ISO Week Number] >= 53 - (4 - CurrentWeek)),
                "Year", 'Date'[Year],
                "Week", 'Date'[ISO Week Number]
            ),
            SELECTCOLUMNS(
                FILTER(ALL('Date'), 'Date'[Year] = CurrentYear && 'Date'[ISO Week Number] < CurrentWeek),
                "Year", 'Date'[Year],
                "Week", 'Date'[ISO Week Number]
            )
        ),
        SELECTCOLUMNS(
            FILTER(ALL('Date'), 'Date'[Year] = CurrentYear && 'Date'[ISO Week Number] >= CurrentWeek - 4 && 'Date'[ISO Week Number] < CurrentWeek),
            "Year", 'Date'[Year],
            "Week", 'Date'[ISO Week Number]
        )
    )
VAR WeeklyPOS = 
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE('POS Data', 'Date'[Year], 'Date'[ISO Week Number]),
            "WeeklyTotal", SUM('POS Data'[POS Amount])
        ),
        TREATAS(PreviousWeeks, 'Date'[Year], 'Date'[ISO Week Number]),
        ALLSELECTED('POS Data'[Product ID])
    )
RETURN
    AVERAGEX(WeeklyPOS, [WeeklyTotal])

关键注意事项 #

• 日期表需标记为日期表（PowerBI中右键日期表→标记为日期表），确保时间上下文识别准确。
• 周编号的规则必须统一（如每周起始日是周一还是周日），POS数据的日期需与日期表的周划分完全匹配，避免统计偏差。
• ALLSELECTED('POS Data'[Product ID])确保度量值能适配不同产品粒度（单品、品类、部门）的筛选需求，不会被全局筛选覆盖。

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