PowerBI DAX 库存余量模型与计算-技术圈

在真实的企业环境中，管理往往是按刚需分配，意思是：如果不出问题，那么就不会真正得到重视，因为说明还没有问题。

在库存管理中，常常面对的难题是：

要可以在无有效批次管理的前提下，知道各种批次余量。

这看着是个矛盾的需求，而却有着很重要的现实意义。

问题重述

在企业中，库存管理中，已经有两个表：

1、采购入库表，它记录了日期，SKU以及入库量。

2、库存盘点表，它记录了日期，SKU以及盘点余量。

问题来了：

如何在不增加额外管理复杂度的前提下，知道现有库存中的某SKU来自哪个批次以及库龄。

先来看看效果：

问题分析

把某SKU的商品或零配件放入仓库是很基本的管理。而必然会涉及到两个表：

1、采购入库表

2、库存盘点表

接下来的问题来了，例如，某日期，我们想知道当时在此前某日期那一批的存货还有多少，怎么办呢？

这就会额外涉及到一个批次管理的概念。

只有将物品的出入对应到相关的批次才能正确计算。

但这就会引入新的管理复杂度。

在实际的企业运行中，很多时候，批次管理是一个复杂的话题，那么，为了简化管理，常常采用：先入先出的方式，将最早入库的内容，当需要取出时，也最先拿出，这确保最新鲜的内容最不容易过期，而存放时间相对较长的也最早得到消耗。

如何实现 - 抽取维度

这里涉及到两个事实表，同属于多事实表问题。

来看看初始的数据模型结构，如下：

在设计和进行 DAX 计算时的大忌就是：直接计算。

本案例可以充分体现这点，要计算每一日对应SKU的余量，会同时涉及：

1、同时涉及两个事实表中的日期；

2、同时涉及两个事实表中的SKU。

那么，到底选择哪个表作为出发点呢？

结果是残忍的都不是。

我们必须从事实表抽取维度，而抽取通用日期维度的方式，大家都很熟悉，这里介绍抽取通用维度的方法：

Product =
DISTINCT(
    UNION(
        SUMMARIZE( '库存盘点表' , [SKU] ),
        SUMMARIZE( '采购入库表' , [SKU] )
    )
)

这种方式，可以兼顾两个表，且确保维度的唯一性。

BI佐罗提醒你注意

在具有主数据管理的情况下，可能会纳入主数据的比对，这超越了本文的范畴，不再说明。

如何实现 - 度量值

在有了有效结构以后，大致可以得到：

而经过仔细分析可以发现，在本例中只有一个日期表是无法正常运作的，我们必须使用第二个日期表来控制计算的参考日期，如下：

形成视图结构如下：

库存数量和入库数量都可以由简单的聚合计算给出，而每次入库所剩余的量以及库龄则比较复杂，效果如下：

其计算给出如下：

KPI.批次余量 =
VAR _date_current = MAX( 'Calendar'[Date] )
VAR _value_current = [KPI.入库数量]
VAR _date_next = CALCULATE( FIRSTNONBLANK( 'Calendar'[Date] , [KPI.入库数量] ) , 'Calendar'[Date] > _date_current )
VAR _date_ref = MAX( CalendarRef[Date] )
VAR _balance  = CALCULATE( LASTNONBLANKVALUE( 'Calendar'[Date] , [KPI.库存数量] ) , 'Calendar'[Date] <= _date_ref )
VAR _inventry = FILTER( ADDCOLUMNS( ALL( 'Calendar'[Date] ) , "@Input" , [KPI.入库数量] ) , [Date] <= _date_ref )
VAR _cummulated_for_current   = SUMX( FILTER( _inventry , [Date] >= _date_current && [Date] <= _date_ref ) , [@Input] )
VAR _cummulated_for_next      = SUMX( FILTER( _inventry , [Date] >= _date_next && [Date] <= _date_ref ) , [@Input] )
RETURN 
    IF( _balance >= _cummulated_for_current , _value_current ,
        IF( _balance >= _cummulated_for_next , _balance - _cummulated_for_next , 0 ) )

可以注意到，如果参考日期是2020-02-18，按照最后一次的盘点应该是 900，再考虑先进先出的原则，那么：

1、2020-02-15 的入库 200，应该未动，还是 200。

2、2020-02-05 的入库 500，应该未动，还是 500。

3、2020-01-18 的入库 400，应该出库 200，还剩 200。

这样，才能确保与最后一次盘点 900 准确对齐。

再来解读该公式的逻辑：

KPI.批次余量 =
VAR _date_current = MAX( 'Calendar'[Date] ) // 视图表格中行日期
VAR _value_current = [KPI.入库数量]              // 视图表格中行值
// 具有入库的下次日期
VAR _date_next = CALCULATE( FIRSTNONBLANK( 'Calendar'[Date] , [KPI.入库数量] ) , 'Calendar'[Date] > _date_current )
// 参考日期
VAR _date_ref = MAX( CalendarRef[Date] )
// 按照参考日期的库存结余
VAR _balance  = CALCULATE( LASTNONBLANKVALUE( 'Calendar'[Date] , [KPI.库存数量] ) , 'Calendar'[Date] <= _date_ref )
// 按照参考日期构建在该日期前的入库表
VAR _inventry = FILTER( ADDCOLUMNS( ALL( 'Calendar'[Date] ) , "@Input" , [KPI.入库数量] ) , [Date] <= _date_ref )
// 按视图表格中行日期计算积累余量
VAR _cummulated_for_current   = SUMX( FILTER( _inventry , [Date] >= _date_current && [Date] <= _date_ref ) , [@Input] )
// 按视图表格中行日期的下次有入库日期，计算积累余量
VAR _cummulated_for_next      = SUMX( FILTER( _inventry , [Date] >= _date_next && [Date] <= _date_ref ) , [@Input] )
RETURN 
    IF( // 如果库存结余比当然行日期后的积累余量还大，说明当前行日期的入库未被动
        _balance >= _cummulated_for_current , _value_current ,
        // 否则，如果库存结余比当然行日期后的积累余量小，但却比下一次有入库后的积累余量大，说明当前行日期的入库被部分使用
        IF(
            _balance >= _cummulated_for_next , _balance - _cummulated_for_next ,
            0 // 全被使用
        )
    )

这就非常清楚了。

看不懂怎么办？

看不懂或者看得懂都是正常的，因为业务逻辑本身就是如此。在熟练熟练使用 DAX 后，需要有两个能力：

1、将业务逻辑转换为 DAX 计算逻辑

2、为业务逻辑的计算提供合理的数据模型支持

再来看库龄的计算就非常简单了：

KPI.库龄天数 =
DATEDIFF( MIN( 'Calendar'[Date] ) ,  MAX( CalendarRef[Date] ) , DAY )

按照入库来显示库龄，可以快速帮助管理者锁定必须要关注的某一批入库，以确保没有过有效期。

总结

管理，有高人说，是一门科学；

管理，有高人说，是一门艺术；

而彼得德鲁克说，管理，既不是科学也不是艺术，而是管理者的实践。

从实战角度，在不引入任何管理复杂度的前提下，仅仅遵守先入先出的规则，并记录好入库表和盘点表，就可以完成管理，这是真实的——实践。

Power BI，尤其是 DAX 的计算，既不是强调技术，也不是强调技巧，而是：

1、可以设计支持业务逻辑表达的数据模型；

2、可以将业务逻辑转为数据的运算逻辑。

订阅BI佐罗讲授的《BI进行时》，还可观看本案例视频解读。

让数据真正成为你的力量

Create value through simple and easy with fun by PowerBI

Excel BI | DAX Pro | DAX 权威指南 | 线下VIP学习

扫码与PBI精英一起学习，验证码：data2020

PowerBI MVP 带你学习 PowerBI
点击“阅读原文”，即刻开始

↙