Power Pivot 先进先出原则库龄库存计算相关

焦棚子的文章目录

需要附件请点赞后,评论区留下邮箱。

一背景

前面写过了一个关于进销存的案例,留一个话题就是先进先出的库存计算。

刚好有朋友提了相关这样的需求。先来看看效果。

效果图

说明:

1、按照先进先出的原则,计算库存的库龄结构,如效果图。

2、特别说明下先进先出,即先入库产品先出库,从而有了库龄。

3、工作中更多会用批次来管理,案例中没有批次,需要写出库龄结构是有些绕。

4、感谢q友阳光岁月提供数据素材。


二、数据源

1、data

原始脱敏数据,大约6万多条,其中数据是erp导出的,需要清洗。


2、维度表:品号

3、日期表

4、事实表:入库

5、事实表:出库

6、关系

7、注意

这个数据里面没有期初库存,即入库汇总即库存总和。


三、上dax

1、入库

入库:=SUM('入库'[入库数量])

2、出库

出库:=SUM('出库'[出库数量])

3、库存

库存:=IF ( '入库'[入库] - '出库'[出库] = 0, BLANK (), '入库'[入库] - '出库'[出库] )

4、库龄0_30

库龄0_30:=
VAR MIND =
    FIRSTDATE ( '日期表'[日期] )
VAR X = 0
VAR Y = 30
VAR MAXD =
    FIRSTDATE ( DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY ) )
VAR LD =
    LASTDATE ( DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY ) )
VAR CURT =
    DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY )
VAR T1 =
    DATESBETWEEN ( '日期表'[日期], MIND, MAXD )
VAR T2 =
    DATESBETWEEN ( '日期表'[日期], MIND, LD )
VAR KC1 =
    CALCULATE ( '入库'[入库], T1 )
VAR KCN =
    CALCULATE ( '入库'[入库], CURT )
VAR KCA =
    CALCULATE ( '入库'[入库], T2 )
VAR CK = '出库'[出库]
VAR RS1 = KC1 - CK
VAR RS2 = KCA - CK
VAR RS =
    SWITCH ( TRUE (), RS1 > 0, KCN, RS2 > 0, RS2, BLANK () )
VAR RST =
    SUMX (
        SUMMARIZECOLUMNS (
            '品号'[品号],
            '品号',
            "T", SWITCH (
                TRUE (),
                CALCULATE ( '入库'[入库], T1 ) - '出库'[出库]
                    > 0, CALCULATE ( '入库'[入库], CURT ),
                CALCULATE ( '入库'[入库], T2 ) - '出库'[出库]
                    > 0, CALCULATE ( '入库'[入库], T2 ) - '出库'[出库],
                BLANK ()
            )
        ),
        [T]
    )
RETURN
    IF ( ISFILTERED ( '品号'[品号] ), RS, RST )

5、库龄30_60,库龄60_90,库龄90_180,库龄180_360,库龄360以上几个逻辑都是一样,只是参数X,Y不一样;如下图。

6、库龄360以上

由于本案例数据只有2018年以后的数据,所以Y参数(见下图)需要特殊处理下

库龄360以上:=
VAR MIND =
    FIRSTDATE ( '日期表'[日期] )
VAR X = 360
VAR Y =
    DATEDIFF ( MIND, TODAY () - 360, DAY )
VAR MAXD =
    FIRSTDATE ( DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY ) )
VAR LD =
    LASTDATE ( DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY ) )
VAR CURT =
    DATESINPERIOD ( '日期表'[日期], TODAY () - X, - Y, DAY )
VAR T1 =
    DATESBETWEEN ( '日期表'[日期], MIND, MAXD )
VAR T2 =
    DATESBETWEEN ( '日期表'[日期], MIND, LD )
VAR KC1 =
    CALCULATE ( '入库'[入库], T1 )
VAR KCN =
    CALCULATE ( '入库'[入库], CURT )
VAR KCA =
    CALCULATE ( '入库'[入库], T2 )
VAR CK = '出库'[出库]
VAR RS1 = KC1 - CK
VAR RS2 = KCA - CK
VAR RS =
    SWITCH ( TRUE (), RS1 > 0, KCN, RS2 > 0, RS2, BLANK () )
VAR RST =
    SUMX (
        SUMMARIZECOLUMNS (
            '品号'[品号],
            '品号',
            "T", SWITCH (
                TRUE (),
                CALCULATE ( '入库'[入库], T1 ) - '出库'[出库]
                    > 0, CALCULATE ( '入库'[入库], CURT ),
                CALCULATE ( '入库'[入库], T2 ) - '出库'[出库]
                    > 0, CALCULATE ( '入库'[入库], T2 ) - '出库'[出库],
                BLANK ()
            )
        ),
        [T]
    )
RETURN
    IF ( ISFILTERED ( '品号'[品号] ), RS, RST )
库龄360以上,参数Y特殊处理。

四、总结

1、老样子业务逻辑转换dax上下文;

2、案例中先进先出运用的是累计计算的思路;

3、案例中总计行需要单独写,使用SUMMARIZECOLUMNS(office2013之前版本请使用SUMMARIZE)聚合,搭配迭代函数SUMX计算。

SUMMARIZECOLUMNS function (DAX) - DAXdocs.microsoft.com图标

附注:

原始数据异常导致
异常数据

本来想把异常数据删掉的,后来想想就留着吧,有兴趣的朋友可以实际操作看看。

注意:

数据动态用的是today来写的,拿到附件以后,得出来的结果和截图自然不一样,如果需要验证,把电脑日期回调至2019年1月13日即可。


by 焦棚子


焦棚子的文章目录

编辑于 2019-10-16