DAX实现先入先出法

思路:

(文末附有文件下载链接)

如果先计算出累计入库数量与累计出库数量，然后为每一笔出库的数量匹配到相应的入库批次，这样计算量显然过大。

在先进先出法下，可以认为结余数量是由最后入库的几批构成的。计算结余数量对应的结余金额则比较简单。

如示例数据中，产品B的第10行，剩余的380件应当包含3月20日的80件，3月5日的70件，2月11日的150件，与2月1日的80件。所以这380件的结余金额为3月20日的16000元+3月5日的21000元+2月11日的24000+2月1日的48000*（80/200），等于80200元。

实现:

要计算出上文的80200元，则需要首先删选原表，以删选出可以构成剩余数量380的那几行入库记录。再为其中部分入库的那一行（最早的那一行），按比例计算出归属于结余金额的部分（48000/200*80）。即，需要构建出上图所示的表格。

想要构建上表，则需要先筛选出2月11日、3月5日、3月20日这三行。再筛选出2月1日这一行，并将其中的200改为80（40%)，然后入库金额也相应改为40%，19200元。

2月11日、3月5日、3月20日这三行即从下往上累计入库数量，累计数量不超过结余数量（380）的那些行。可以构建下图所示的表格，再对表格进行筛选后得到。

有了初步的思路，现在可以开始用DAX来实现这个想法了。

步骤一：

首先，删选出[入库数量]不为空，产品为当前产品，发生时序不晚于当前行时序（index小于等于当前的index）的部分。然后为前一步删选出的表格，增加一列计算列[不足数量]（即，从下往上累计的入库数量少于结存数量的量）。要实现从下往上累加，也就是对Index大于当前迭代的index的行进行求和。再对上面的结果进行筛选，筛选出[不足数量]大于等于0的部分，就是上文所说的“2月11日、3月5日、3月20日这三行即从下往上累计入库数量，累计数量不超过结余数量（380）的那些行”。因为在单元格内，无法直接显示一张张的表，所以对计算表的[入库数量]列进行求和，以检验结果是否正确。

添加计算列**[步骤一检验数]**

步骤一检验数 = 
VAR vRest= 'F02先进先出法'[结余数量]
VAR vName='F02先进先出法'[名称]
VAR vNum='F02先进先出法'[index]
VAR vCumDiff=
    ADDCOLUMNS(
        FILTER(
            ALL('F02先进先出法'),
            'F02先进先出法'[名称]=vName
            	&&'F02先进先出法'[index]<=vNum
            	&& not ISBLANK('F02先进先出法'[入库数量])
        ),
        //删选了[入库数量]不为空，产品为当前产品，发生时序不晚于当前行时序（index小于当前的index）的部分
        "不足数量",
        VAR vNumInner='F02先进先出法'[index]
        RETURN 
        vRest-
            SUMX(   //实现从下往上累加[入库数量]
                FILTER(
                    ALL('F02先进先出法'),
                    'F02先进先出法'[名称]=vName&&'F02先进先出法'[index]<=vNum&&'F02先进先出法'[index]>=vNumInner
                ),
                [入库数量]
            )
    )
VAR vAllIn=
    FILTER(
        vCumDiff,
        [不足数量]>=0
    ) //筛选出了入库数量全归于结余数量的行
VAR vAllInQuantity=SUMX(vAllIn,[入库数量])  //对[入库数量]求和，以便检查是否正确。
RETURN
    vAllInQuantity

检查结果,最后第一行300=80+70+150，最后第二行500=80+70+150+200，最后第3行420=70+150+200。第一步的结果是正确。

步骤二：

以计算产品B第10行的结余金额为例，到现在我们已经求得了下面的三行。还缺最上面的那行，部分归入结余数量的那行。

以计算产品B第10行的结余金额为例，[步骤一检验数] 中的表格变量vCumDiff 如下图所示。我们需要那一行就是不足数量<0的行中，不足数量最大的那一行。

使用TOPN筛选出该行，因为不能显示表，所以计算出该行[日期]列的值，以检验是否正确。在步骤一的计算下添加以下代码，以实现筛选该行，并找出[日期]进行检验。

VAR vPartIn=
    TOPN(
        1,
        FILTER(
            vCumDiff,
            [不足数量]<0
        ),
        [不足数量],
        DESC
    )
VAR test=SELECTCOLUMNS(vPartIn,"A",'F02先进先出法'[入库数量])
RETURN
    test

检验无误。

步骤三：

以计算产品B第10行的结余金额为例。接下来计算步骤一筛选出的3行中的入库金额，与步骤二筛选出的1行中应该归入结余金额的部分入库金额。

VAR vAllInAmount=
    SUMX(
        vAllIn,
        [入库金额]
    )  //示例中的3行对应的金额
//归属结余的数量乘以入库单价，即为归属于结余的金额
VAR vPartInAmount= SELECTCOLUMNS(vPartIn,"A",[入库单价])*(vRest-vAllInQuantity)
VAR vRestAmount=vAllInAmount + vPartInAmount
RETURN
    vRestAmount

检验无误。至此，[步骤一检验数] [步骤二检验数] 这两个计算列就可以删除了。

步骤四:

[入库金额]与[结余金额]都已经有了，现在还缺出库金额。计算出库金额可以使用出库前，也就是出库行的上一行的结余金额，减于出库后的结余金额而得。

出库金额 = 
VAR vName='F02先进先出法'[名称]
VAR vNum='F02先进先出法'[index]
VAR vAmount=
    SELECTCOLUMNS(
        FILTER(
            ALL('F02先进先出法'),
            'F02先进先出法'[名称]=vName
                &&'F02先进先出法'[index]=vNum-1
        ),
        "A",
        'F02先进先出法'[结余金额]
    )-'F02先进先出法'[结余金额]
RETURN 
    IF( 
        NOT ISBLANK('F02先进先出法'[出库数量]),
        vAmount
    )

附件下载：

• 坚果云

• 百度网盘