R/Power BI按分类分组计算Actuals列4周滚动平均值

计算规则 #

• 分组维度：按State（州）字段拆分，每个州独立计算滚动值
• 窗口范围：从每条记录的所属周次倒推，覆盖连续4个自然周
• 缺失值规则：窗口范围内无销售记录的周次，Actuals（实际值）按0计入统计
• 校验示例：样例数据中TX州2022年5月29日当周的4周平均值 = (1 + 0（5月22日当周无销售记录） + 1 + 3) / 4 = 1.25

测试数据集 #

Week    State   Actuals
4/24/2022   CA  1
5/1/2022    CA  3
5/8/2022    CA  34
5/8/2022    NV  2
5/8/2022    AZ  1
5/8/2022    TX  3
5/15/2022   CA  27
5/15/2022   TX  1
5/22/2022   CA  15
5/29/2022   TX  1
5/29/2022   CA  24

R语言实现方案 #

核心逻辑是先补全每个州所有连续自然周的记录，缺失周的Actuals填0，再基于补全后的完整数据集做滚动窗口计算，避免原始数据断周导致窗口偏移。

# 加载依赖包
library(tidyverse)
library(lubridate)
library(zoo)

# 读入原始数据并转换日期格式
raw_df <- tribble(
  ~Week, ~State, ~Actuals,
  "4/24/2022", "CA", 1,
  "5/1/2022", "CA", 3,
  "5/8/2022", "CA", 34,
  "5/8/2022", "NV", 2,
  "5/8/2022", "AZ", 1,
  "5/8/2022", "TX", 3,
  "5/15/2022", "CA", 27,
  "5/15/2022", "TX", 1,
  "5/22/2022", "CA", 15,
  "5/29/2022", "TX", 1,
  "5/29/2022", "CA", 24
) %>% mutate(Week = mdy(Week))

# 生成所有州+所有连续周的完整组合，缺失周Actuals填0
full_df <- raw_df %>%
  complete(Week = seq.Date(min(Week), max(Week), by = "week"), State, fill = list(Actuals = 0)) %>%
  arrange(State, Week)

# 按州分组计算右对齐4周滚动平均值
result <- full_df %>%
  group_by(State) %>%
  mutate(rolling_4w_avg = rollmean(Actuals, k = 4, align = "right", fill = NA)) %>%
  ungroup()

运行代码后可校验：TX州2022-05-29对应的rolling_4w_avg值为1.25，和规则要求完全一致。如果只需要保留原始数据中存在销售记录的行，将result和原始raw_df按Week、State做内连接即可。

Power BI实现方案 #

1. 进入Power Query编辑器：将Week列转换为日期类型，生成从数据集最小周到最大周的连续周日期表，同时提取State列的不重复值生成州维度表，将两张表做笛卡尔积得到所有州+所有周的完整组合
2. 将原始销售表和上述完整组合做左连接，连接键为Week、State，将连接后Actuals为空的行填充为0，加载为计算用事实表
3. 编写DAX度量值计算滚动平均：

4周滚动平均 = 
CALCULATE(
    DIVIDE(SUM('销售事实表'[Actuals]),4),
    DATESINPERIOD('日期表'[Week], MAX('销售事实表'[Week]), -28, DAY),
    ALLEXCEPT('销售事实表', '销售事实表'[State])
)

注意：不要跳过补全断周填0的步骤，否则DAX时间智能函数会自动跳过无数据的周，导致窗口覆盖的周数不符合要求。

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