在PowerBI中以先进先出规则计算出入库核对表

先进先出(FIFO)是企业管理中广泛应用的库存计价方法，尤其在财务核算和成本分析中扮演着重要角色。本篇文章将介绍如何在PowerBI中实现基于先进先出规则的出入库核对表。

问题描述

按先进先出规则，找出各产品出库时所用的入库批次以及对应的出库数量。例如：对于出库编号为5的200件出库产品来说，其中的90件来自入库编号为1所剩下的产品，然后不够的110件就使用入库编号为2的。

具体问题如下图所示：

本案例的初始数据如下：

入库：

入库日期	入库编号	入库产品	入库总数
2022年9月1日	1	ID01	1000
2022年9月11日	2	ID01	200
2022年10月10日	3	ID01	100
2022年10月11日	4	ID01	1000
2022年10月1日	5	ID02	20
2022年10月11日	6	ID02	100

出库：

出库日期	出库编号	出库产品	出库总数
2022年9月10日	1	ID01	500
2022年9月11日	2	ID01	110
2022年9月30日	3	ID01	200
2022年10月1日	4	ID01	100
2022年10月10日	5	ID01	200
2022年10月20日	6	ID01	230
2022年10月21日	7	ID02	100

若需其它辅助表等，可自行创建并建模。

解题要点

因为DAX中并不支持递归，因此在面对先进先出等场景时，需要找到一种数学上能计算的方法来处理。然后在本案例中需要注意的是，查找某个产品出库时所用的入库批次以及对应的出库数量时，必须要考虑到在前面出库的产品对库存的影响，否则计算结果将不正确，而这也是本案例的难点。

由于思路较复杂且难以描述，因此请直接从下文查看给出的示例答案来理解。

解决方案

首先，数据模型如下图所示：

然后，创建如下度量值：

本次出库数量 = 
MAXX(
    OFFSET(
        0,
        GENERATEALL(
            '出库',
            VAR TotalOutStock = '出库'[出库总数]
            VAR CurDate_Out = '出库'[出库日期]
            VAR CurProduct_Out = '出库'[出库产品]
            VAR vTb1 = FILTER(ALL('入库'),'入库'[入库日期]<=CurDate_Out && '入库'[入库产品]=CurProduct_Out)
            VAR vTb2 = 
                ADDCOLUMNS(
                    vTb1,
                    "CumRemaining",
                        VAR CurDate_In = '入库'[入库日期]
                        VAR CumInventory = SUMX(FILTER(vTb1,'入库'[入库日期]<=CurDate_In),'入库'[入库总数])
                        VAR CumOutStock = 
                            SUMX(
                                FILTER(ALL('出库'),'出库'[出库产品]=CurProduct_Out && '出库'[出库日期]<CurDate_Out),
                                '出库'[出库总数]
                            )
                        RETURN
                        MAX(CumInventory-CumOutStock,0)
                )
            VAR vTb3 = 
                FILTER(
                    vTb2,
                    VAR CurDate_In = '入库'[入库日期]
                    VAR MaxCumRemaining = MAXX(FILTER(vTb2,'入库'[入库日期]<CurDate_In),[CumRemaining])
                    RETURN
                    AND(
                        AND(
                            [CumRemaining]>0,
                            OR(
                                [CumRemaining]>MAXX(TOPN(1,vTb2,'入库'[入库日期],1),[CumRemaining]),
                                '入库'[入库日期]=MINX(vTb2,'入库'[入库日期])
                            )
                        ),
                        [CumRemaining]>MaxCumRemaining
                    )
                )
            VAR vTb4 = 
                FILTER(
                    ADDCOLUMNS(
                        vTb3,
                        "OutStock",
                            VAR CurDate = '入库'[入库日期]
                            VAR CumRemaining = [CumRemaining]
                            VAR PreCumRemaining = MAXX(TOPN(1,FILTER(vTb3,'入库'[入库日期]<CurDate),'入库'[入库日期]),[CumRemaining])
                            RETURN
                            IF(TotalOutStock>PreCumRemaining,MIN(CumRemaining-PreCumRemaining,TotalOutStock-PreCumRemaining))
                    ),
                    [OutStock]>0
                )
            RETURN
            SUMMARIZE(vTb4,'入库'[入库编号],[OutStock])
        ),
        MATCHBY('入库'[入库编号])
    ),
    [OutStock]
)   

然后创建一个矩阵，并将出库表中的所有字段以及入库表的入库编号字段一起作为行标签，再将上面的度量值放入矩阵的值字段即可，如下图所示：

总结

以上方法仅供参考，若有更优雅的解决方案，欢迎留言讨论，或者加入我们的技术交流群，一起享受这种思维碰撞的快乐吧！

PBI/DAX技术交流群(QQ)：344353627