Power BI 使用 DAX 按照先进先出原则计算毛利润

182_Power BI 使用 DAX 按照先进先出原则计算毛利润

一、背景

在笔者以往的文章中也有先进先出的案例，可以参考(https://jiaopengzi.com/?s=%E5%85%88%E8%BF%9B%E5%85%88%E5%87%BA)。

今天我们来看一个网友提出的问题，

• 先进先出的原则，
• 入库和出库的价格都是不统一的。
• 按照出库的日期来计算先进先出的sku的毛利(fifo_profit)。

如下：

​

二、DAX

表间关系

我们这里按照正常业务逻辑需要有日期表：calendar、入库表：storage、出库表：sale。

关系如下：

关键的 DAX 如下，@result为需要的结果：

DEFINE
/*先进先出毛利润计算*/
VAR tableSale =  ALL( 'sale' )
VAR tbaleStorage =  ALL ( 'storage' )
VAR tableIn =
    /*计算每条入库记录的累计入库：@cumIn*/
    ADDCOLUMNS (
        tbaleStorage,
        "@cumIn",
            VAR dateIn = [date]
            VAR skuIn = [sku]
            VAR tableInX =
                FILTER ( tbaleStorage, [sku] = skuIn && [date] <= dateIn )
            RETURN
                SUMX ( tableInX, [units] )
    )
VAR tableOut =
    /*计算每条出库记录的累计出库：@cumOut*/
    ADDCOLUMNS (
        tableSale,
        "@cumOut",
            VAR dateOut = [date]
            VAR skuOut = [sku]
            VAR tableOutX =
                FILTER ( tableSale, [sku] = skuOut && [date] <= dateOut )
            RETURN
                SUMX ( tableOutX, [units] )
    )  
VAR tableResult =
    /*掐头去尾找到出库和入库的对应关系，计算出销售金额和入库成本之间的差异计算出毛利润*/
    ADDCOLUMNS (
        tableOut,
        "@result",
            VAR dateOut = [date]
            VAR cumOut = [@cumOut]
            VAR skuAc = [sku]
            VAR tableOutPre =
                FILTER ( tableOut, [sku] = skuAc && [date] < dateOut ) //前一个@累计出库
            VAR preMaxCumOut =
                MAXX ( tableOutPre, [@cumOut] )
            VAR tableInStartEnd = // 先进先出规则下，出库数据对应到入库数据
                FILTER (
                    tableIn,
                    [sku] = skuAc && [date] <= dateOut && [@cumIn] >= preMaxCumOut
                )
            VAR tableInOut =// 计算每次入库记录对应到出库的数量
                ADDCOLUMNS (
                    tableInStartEnd,
                    "@unitOut",
                        VAR dateMin =
                            MINX ( tableInStartEnd, [date] )
                        RETURN
                            SWITCH (
                                TRUE (),
                                cumOut > [@cumIn] && [date] = dateMin, [@cumIn] - preMaxCumOut,
                                cumOut > [@cumIn], [units],
                                cumOut - [@cumIn] + [units]
                            )
                )
            VAR tableOkCum =  FILTER ( tableInOut, [@cumIn] >= cumOut ) // 每条记录 满足累计入库大于等于出库
            VAR dateMin =MINX ( tableOkCum, [date] )
            VAR tableOkDate = FILTER ( tableInOut, [date] <= dateMin ) // 每条记录 日期小于等于 dateMin
            VAR cost = SUMX ( tableOkDate, [price] * [@unitOut] ) // 计算出库成本
            VAR profit = [price] * [units] - cost // 计算毛利
            RETURN
                profit
    )
EVALUATE
tableResult

三、总结

1. 先进先出的主要方法是"掐头去尾"，找到出库信息对应到的入库信息。
2. 按照每行记录计算出库和入库数量累计值，再通过迭代函数计算对应的入库成本和出库销售额，从而通过差异计算出毛利。
3. 由于业务嵌套层数比较多，可以利用 Dax Studio 调试，多使用 CONCATENATEX。

附件下载

https://jiaopengzi.com/3026.html

请关注

全网同名搜索 焦棚子

如果对你有帮助，请 点赞、关注、三连 支持一下，这是我们更新的动力。

我们承接 Power BI 相关业务：培训、报告制作与部署、咨询服务等。

by 焦棚子