基于DAX(PowerBI)或PowerQuery实现跨表日期差月均聚合计算

现有两张表：

1. 日历表（Date列存储月份文本，如Dec 2021）

Date
-----
Dec 2021
Jan 2022
Feb 2022
Mar 2022
...

2. 事件表

Event   | Last Date
-----------------
Event A | 01-Jan-2013
Event B | 01-Mar-2017
Event C | 01-Feb-2022
...

需要生成一张聚合表，按日历月份计算符合条件事件的月份差平均值，规则：

• 仅纳入Last Date ≤ 当前日历月份的事件
• 月份差 = 当前日历月份与事件Last Date的月份间隔数（如Dec 2021与01-Jan-2013的月份差为107）
• 平均值为所有符合条件事件的月份差的算术平均

输出表样式：

Date     | Avg Diff
------------------------
Dec 2021 | 82
Jan 2022 | 83
Feb 2022 | 56
Mar 2022 | 57
...

步骤1：预处理日历表日期列 #

如果日历表的Date是文本类型，先将其转换为日期格式（建议取当月最后一天，确保月份计算准确）：

日历表_日期转换 = 
ADDCOLUMNS(
    '日历表',
    "日历日期", EOMONTH(DATEVALUE('日历表'[Date]), 0)
)

步骤2：创建计算表 #

使用以下DAX表达式生成最终结果表：

平均月份差表 = 
ADDCOLUMNS(
    '日历表_日期转换',
    "Avg Diff",
    VAR CurrentMonth = '日历表_日期转换'[日历日期]
    RETURN
    AVERAGEX(
        FILTER(
            '事件表',
            '事件表'[Last Date] <= CurrentMonth
        ),
        DATEDIFF('事件表'[Last Date], CurrentMonth, MONTH)
    )
)

说明 #

• EOMONTH：将文本月份转为当月最后一天的日期，避免因日期日数差异导致计算误差
• FILTER：筛选出Last Date不晚于当前日历月份的事件
• DATEDIFF：计算两个日期之间的月份间隔数
• AVERAGEX：对符合条件的事件逐个计算月份差后取平均值

步骤1：转换日历表日期格式 #

1. 加载日历表到PowerQuery，选中Date列
2. 点击转换选项卡 → 数据类型 → 日期（自动识别为当月第一天，可手动调整为当月最后一天：添加列 → 自定义列，公式为Date.EndOfMonth([Date])）

步骤2：转换事件表日期格式 #

加载事件表到PowerQuery，选中Last Date列，转换为日期类型

步骤3：交叉合并表 #

1. 在日历表的PowerQuery编辑器中，点击合并查询 → 合并为新查询
2. 选择事件表，合并条件选无（生成笛卡尔积，即每个日历月份对应所有事件）
3. 展开合并后的事件表列，保留Event和Last Date

步骤4：筛选符合条件的行 #

添加自定义列，公式为：

if [Last Date] <= [日历日期] then true else false

筛选该列值为true的行

步骤5：计算月份差 #

添加自定义列，公式为：

Date.MonthDifference([Last Date], [日历日期])

步骤6：分组计算平均值 #

1. 点击转换选项卡 → 分组依据
2. 分组列选择Date（或转换后的日历日期），新列名设为Avg Diff，操作选平均值，列选择刚计算的月份差列

步骤7：调整输出格式 #

将日历日期列转换回文本格式（如Dec 2021），最终导出到Power BI模型

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