Power BI实现按人员月度计算金额扣除及特殊规则处理

需求梳理 #

• 每个人员初始金额固定为1040，按月度记录顺序依次扣除MONEY PER MONTH列的值，直到余额耗尽
• 特殊规则：当计算到第5个月时，若剩余金额（初始1040减去前4个月累计扣除）小于20，需做以下调整：
  • 第5个月的扣除金额固定设为20
  • 第4个月扣除后剩余金额强制设为240（即调整第4个月的扣除金额，使得前4个月累计扣除为1040-20=1020）

实现步骤 #

1. 添加月度序号辅助列 #

首先需要给每个人员的月度记录按时间排序，创建月度序号计算列：

月度序号 = RANKX(FILTER('你的表名', '你的表名'[人员ID] = EARLIER('你的表名'[人员ID])), '你的表名'[START],, ASC, DENSE)

说明：人员ID替换为你表中唯一标识人员的列名，START为记录的起始日期列，确保每个人员的记录按时间从早到晚生成1-5的序号。

2. 创建目标计算列 #

使用以下DAX表达式创建目标列，替换其中的表名和列名为你的实际值：

目标列 = 
VAR 当前人员 = '你的表名'[人员ID]
VAR 当前月度序号 = '你的表名'[月度序号]
-- 计算当前人员前N-1个月的正常累计扣除金额
VAR 前序累计正常 = 
    SUMX(
        FILTER('你的表名', '你的表名'[人员ID] = 当前人员 && '你的表名'[月度序号] < 当前月度序号),
        '你的表名'[MONEY PER MONTH]
    )
-- 计算前4个月的正常累计扣除金额
VAR 前4个月累计正常 = 
    SUMX(
        FILTER('你的表名', '你的表名'[人员ID] = 当前人员 && '你的表名'[月度序号] <= 4),
        '你的表名'[MONEY PER MONTH]
    )
-- 判断是否触发特殊规则：初始金额减前4个月累计后剩余不足20
VAR 触发特殊规则 = 1040 - 前4个月累计正常 < 20
RETURN
SWITCH(
    TRUE(),
    -- 第5个月逻辑：触发规则则返回20，否则返回剩余金额
    当前月度序号 = 5, IF(触发特殊规则, 20, MAX(0, 1040 - 前4个月累计正常)),
    -- 第4个月逻辑：触发规则则调整扣除金额，使得扣除后剩余240
    当前月度序号 = 4 && 触发特殊规则, 1040 - SUMX(FILTER('你的表名', '你的表名'[人员ID] = 当前人员 && '你的表名'[月度序号] <= 3), '你的表名'[MONEY PER MONTH]) - 240,
    -- 前3个月逻辑：按正常规则扣除，不超过剩余金额
    MIN(1040 - 前序累计正常, '你的表名'[MONEY PER MONTH])
)

逻辑说明 #

• 前3个月按初始金额依次扣除MONEY PER MONTH，扣除金额不超过当前剩余余额
• 若第5个月正常计算时剩余金额不足20，强制第5个月扣除20，同时调整第4个月的扣除金额，保证前4个月累计扣除为1020（1040-20），即第4个月扣除后剩余240
• 每个人员的初始金额独立重置为1040，不受其他人员数据影响

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