PowerBI中如何计算已采购未售出产品的成本

需求是计算已采购但未售出产品的总数量及对应成本金额，现有模型中dim_product通过product_id关联销售表fact_sale_order_line和采购表fact_purchase_order_line，数量计算逻辑没问题，但当前DAX代码返回的是总采购金额，而非未售出部分的成本。

问题分析 #

原代码的核心问题是直接取用了当期总采购金额，没有将成本按未售出数量的比例进行分配，导致结果不符合预期。

解决方案：加权平均单位成本法 #

最通用的做法是先计算当期采购的加权平均单位成本，再乘以未售出数量，得到未售出产品的成本。修正后的DAX代码如下：

€_enero_2021 = 
// 计算2021年1月采购总数量
VAR units_purchased =
    CALCULATE(
        SUM(fact_purchase_order_line[product_quantity]),
        fact_purchase_order_line[order_date_id] >= 20210101,
        fact_purchase_order_line[order_date_id] <= 20210131
    )
// 计算2021年1月销售总数量
VAR units_sold =
    CALCULATE(
        SUM(fact_sale_order_line[product_quantity]),
        fact_sale_order_line[order_date_id] >= 20210101,
        fact_sale_order_line[order_date_id] <= 20210131
    )
// 未售出产品数量
VAR units_purchased_not_sold = units_purchased - units_sold
// 2021年1月总采购金额
VAR total_purchase_amount =
    CALCULATE(
        SUM(fact_purchase_order_line[purchase_amount]),
        fact_purchase_order_line[order_date_id] >= 20210101,
        fact_purchase_order_line[order_date_id] <= 20210131
    )
// 加权平均单位采购成本（用DIVIDE避免除以0）
VAR avg_unit_cost = DIVIDE(total_purchase_amount, units_purchased, 0)
// 计算未售出产品的成本
VAR result =
    IF(
        units_purchased_not_sold <= 0,
        BLANK(),
        units_purchased_not_sold * avg_unit_cost
    )
RETURN result

代码说明 #

1. 先分别计算当期的采购总数量、销售总数量，得到未售出数量；
2. 计算当期总采购金额，结合采购总数量算出加权平均单位成本；
3. 用未售出数量乘以平均单位成本，得到未售出部分的成本；
4. 通过IF判断，如果未售出数量≤0则返回空值，避免出现负数成本。

进阶：先进先出（FIFO）成本核算 #

如果需要更精确的成本匹配（比如按采购顺序先卖最早采购的产品），可以实现FIFO逻辑，但需要按采购日期对采购记录排序并累计数量，再匹配销售数量对应的采购批次成本。示例逻辑如下（需根据实际模型调整）：

// FIFO逻辑简化示例，需结合上下文使用
VAR purchase_ranked =
    ADDCOLUMNS(
        fact_purchase_order_line,
        "Cumulative_Qty", 
        CALCULATE(
            SUM(fact_purchase_order_line[product_quantity]),
            FILTER(
                ALL(fact_purchase_order_line),
                fact_purchase_order_line[product_id] = EARLIER(fact_purchase_order_line[product_id]) &&
                fact_purchase_order_line[order_date_id] <= EARLIER(fact_purchase_order_line[order_date_id])
            )
        )
    )
VAR sold_qty = units_sold
VAR unsold_cost =
    SUMX(
        FILTER(purchase_ranked, [Cumulative_Qty] > sold_qty),
        ([Cumulative_Qty] - sold_qty) * ([purchase_amount]/[product_quantity])
    )

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