Power BI技术需求：查询返回0时替换前值+月度金额拆分逻辑

核心需求拆解 #

• 月度拆分：每月固定扣除260；若扣除后剩余金额不足20，当月取值为20，并从之前的月度扣除金额中抵扣这20
• 0值处理：查询结果为0时，自动替换为前一个非0值

一、SQL实现方案（以MySQL/PostgreSQL为例） #

假设你的金额表为 amount_table，包含字段 id（记录唯一标识）和 initial_amount（初始总金额）。

1. 生成月度序列 #

通过递归CTE生成所需的月度数，覆盖所有可能的拆分月份：

WITH RECURSIVE months AS (
    SELECT 1 AS month_num
    UNION ALL
    SELECT month_num + 1 
    FROM months
    WHERE month_num <= (SELECT CEIL((initial_amount + 20) / 260) FROM amount_table)
)

2. 计算月度扣除金额并处理不足20的抵扣逻辑 #

WITH RECURSIVE months AS (
    SELECT 1 AS month_num
    UNION ALL
    SELECT month_num + 1 
    FROM months
    WHERE month_num <= (SELECT CEIL((initial_amount + 20) / 260) FROM amount_table)
),
raw_calculation AS (
    SELECT
        month_num,
        initial_amount,
        -- 初步计算当月扣除额
        CASE
            WHEN initial_amount - 260*(month_num-1) >= 260 THEN 260
            WHEN initial_amount - 260*(month_num-1) > 20 THEN initial_amount - 260*(month_num-1)
            ELSE 20
        END AS temp_deduct,
        -- 计算扣除前剩余金额
        initial_amount - 260*(month_num-1) AS remaining_before
    FROM months, amount_table
),
adjusted_deduct AS (
    SELECT
        month_num,
        temp_deduct,
        remaining_before,
        -- 标记是否需要从之前的月份抵扣
        CASE WHEN remaining_before <= 20 THEN 1 ELSE 0 END AS adjust_flag
    FROM raw_calculation
)
-- 最终计算调整后的月度金额
SELECT
    month_num,
    CASE
        WHEN adjust_flag = 1 THEN 20
        -- 累计前面需要抵扣的次数，从当前扣除额中减去对应金额
        ELSE temp_deduct - COALESCE(SUM(adjust_flag) OVER (ORDER BY month_num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)*20, 0)
    END AS monthly_amount
FROM adjusted_deduct
-- 过滤掉扣除后无意义的行
WHERE (remaining_before - temp_deduct >= 0) OR adjust_flag = 1;

3. 0值替换为前一个非0值 #

使用窗口函数 LAG() 获取前一个有效值，替换当前0值：

WITH final_monthly AS (
    -- 这里放入上面计算得到的monthly_amount结果集
    SELECT month_num, monthly_amount FROM adjusted_deduct_result
)
SELECT
    month_num,
    CASE
        WHEN monthly_amount = 0 THEN LAG(monthly_amount) OVER (ORDER BY month_num)
        ELSE monthly_amount
    END AS final_amount
FROM final_monthly;

二、Excel实现方案 #

如果使用Excel手动处理：

1. 生成月度序列：在A列输入1、2、3...，下拉填充至所需月份数
2. 计算月度扣除额：
   • B1单元格输入初始总金额
   • C1单元格输入公式：=IF(B1>=260,260,IF(B1>20,B1,20))
   • B2单元格输入公式：=B1-C1，下拉填充B列
   • C2单元格输入公式：=IF(B2>=260,260,IF(B2>20,B2,20))，下拉填充C列
3. 0值替换：
   • D1单元格输入：=C1
   • D2单元格输入公式：=IF(C2=0,D1,C2)，下拉填充D列

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