基于DAX实现自定义促销期间的年度同比累计销售额计算与可视化

刚好之前做过类似的零售促销数据对比需求，给你梳理一套落地的步骤，从数据准备到可视化全流程都覆盖到：

1. 先把数据结构捋顺 #

首先得保证两张核心表的结构能支撑后续计算：

• 促销期表：建议不要每年换表，而是在同一张表里用年份字段区分，字段至少包含：促销期名称（比如「夏季大促2024」）、开始日期、结束日期、年份。这样每年只需要追加新的促销期数据，不用重构表结构。
• 销售额表：必须包含销售日期、销售额，如果有门店/品类维度也可以加上，方便后续做细分对比。

2. 计算所选促销期的本年累计销售额 #

这里分两种常用工具的实现方式：

SQL 实现（适合数据库存储数据的场景） #

假设要计算「夏季大促2024」的累计销售额，直接通过日期关联两张表求和：

SELECT 
    p.promo_name AS 促销期名称,
    ROUND(SUM(s.sales_amount), 2) AS 本年累计销售额
FROM promo_periods p
JOIN sales_data s 
    ON s.sale_date BETWEEN p.start_date AND p.end_date
WHERE p.promo_name = '夏季大促2024'
GROUP BY p.promo_name;

Excel 实现（适合本地表格数据的场景） #

用SUMIFS结合VLOOKUP动态匹配促销期的起止日期：

=SUMIFS(
    销售额!$B:$B,
    销售额!$A:$A, ">="&VLOOKUP("夏季大促2024", 促销期表!$A:$C, 2, FALSE),
    销售额!$A:$A, "<="&VLOOKUP("夏季大促2024", 促销期表!$A:$C, 3, FALSE)
)

3. 匹配并计算去年同期的累计值 #

重点是精准匹配去年对应的促销期（因为每年起止日期可能不同，不能直接用日期减一年）：

SQL 实现 #

通过提取促销期名称的前缀（比如去掉末尾的年份），匹配去年同类型的促销期：

WITH current_promo AS (
    SELECT 
        promo_name,
        start_date,
        end_date,
        -- 提取促销期前缀，假设名称格式是「促销类型+年份」
        LEFT(promo_name, LEN(promo_name)-4) AS promo_type
    FROM promo_periods
    WHERE promo_name = '夏季大促2024'
)
SELECT 
    cp.promo_name AS 本年促销期,
    ROUND(SUM(cs.sales_amount), 2) AS 本年累计,
    lp.promo_name AS 去年同期促销期,
    ROUND(SUM(ls.sales_amount), 2) AS 去年累计,
    -- 计算同比增长率
    ROUND((SUM(cs.sales_amount)-SUM(ls.sales_amount))/SUM(ls.sales_amount)*100, 2) AS 同比增长率(%)
FROM current_promo cp
JOIN sales_data cs 
    ON cs.sale_date BETWEEN cp.start_date AND cp.end_date
-- 关联去年同类型的促销期
JOIN promo_periods lp 
    ON lp.promo_type = cp.promo_type 
    AND lp.year = YEAR(cp.start_date)-1
JOIN sales_data ls 
    ON ls.sale_date BETWEEN lp.start_date AND lp.end_date
GROUP BY cp.promo_name, lp.promo_name;

Excel 实现 #

先提取促销期前缀，再匹配去年的起止日期计算累计：

1. 提取前缀：=LEFT(A2, LEN(A2)-4)（A2是促销期名称单元格）
2. 计算去年累计：

=SUMIFS(
    销售额!$B:$B,
    销售额!$A:$A, ">="&VLOOKUP(LEFT(A2,LEN(A2)-4)&(YEAR(VLOOKUP(A2,促销期表!$A:$C,2,FALSE))-1), 促销期表!$A:$C, 2, FALSE),
    销售额!$A:$A, "<="&VLOOKUP(LEFT(A2,LEN(A2)-4)&(YEAR(VLOOKUP(A2,促销期表!$A:$C,2,FALSE))-1), 促销期表!$A:$C, 3, FALSE)
)

4. 对比结果可视化 #

• BI工具（Tableau/Power BI）：把本年、去年的累计值以及同比增长率拖入画布，选择簇状柱状图（直观展示数值差异）或者折线图（看趋势），把促销期名称作为维度，还可以添加筛选器让用户自由选择不同促销期。
• Excel：把本年、去年累计值放在同一行，插入柱状图，添加数据标签，再把同比增长率放在旁边的单元格，用条件格式（比如红色下降、绿色上升）标注，让对比结果一目了然。

• 促销期名称的命名规则一定要统一，比如「XX大促YYYY」，这样提取前缀匹配去年同期才不会出错。
• 如果遇到跨年促销期（比如「新年大促2024」从2023年12月到2024年1月），要确保销售额数据的日期范围覆盖完整，避免漏算。
• 尽量把查询做成动态参数化的，比如SQL用存储过程接收促销期名称参数，Excel用数据验证下拉框选择促销期，这样每年换促销期数据后不用手动修改公式或代码。

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