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 的计算，既不是强调技术，也不是强调技巧，而是：
订阅BI佐罗讲授的《BI进行时》，还可观看本案例视频解读。
让数据真正成为你的力量
Create value through simple and easy with fun by PowerBI
Excel BI | DAX Pro | DAX 权威指南 | 线下VIP学习
扫码与PBI精英一起学习，验证码：data2020
PowerBI MVP 带你学习 PowerBI
点击“阅读原文”，即刻开始
↙