PowerBI DAX 庫存余量模型與計算
在真實的企業(yè)環(huán)境中，管理往往是按剛需分配，意思是：如果不出問題，那么就不會真正得到重視，因為說明還沒有問題。
在庫存管理中，常常面對的難題是：
要可以在無有效批次管理的前提下，知道各種批次余量。
這看著是個矛盾的需求，而卻有著很重要的現(xiàn)實意義。
問題重述
在企業(yè)中，庫存管理中，已經(jīng)有兩個表：
1、采購入庫表，它記錄了日期，SKU以及入庫量。
2、庫存盤點表，它記錄了日期，SKU以及盤點余量。
問題來了：
如何在不增加額外管理復雜度的前提下，知道現(xiàn)有庫存中的某SKU來自哪個批次以及庫齡。
先來看看效果：
問題分析
把某SKU的商品或零配件放入倉庫是很基本的管理。而必然會涉及到兩個表：
1、采購入庫表
2、庫存盤點表
接下來的問題來了，例如，某日期，我們想知道當時在此前某日期那一批的存貨還有多少，怎么辦呢？
這就會額外涉及到一個批次管理的概念。
只有將物品的出入對應到相關的批次才能正確計算。
但這就會引入新的管理復雜度。
在實際的企業(yè)運行中，很多時候，批次管理是一個復雜的話題，那么，為了簡化管理，常常采用：先入先出的方式，將最早入庫的內(nèi)容，當需要取出時，也最先拿出，這確保最新鮮的內(nèi)容最不容易過期，而存放時間相對較長的也最早得到消耗。
如何實現(xiàn) - 抽取維度
這里涉及到兩個事實表，同屬于多事實表問題。
來看看初始的數(shù)據(jù)模型結構，如下：
在設計和進行 DAX 計算時的大忌就是：直接計算。
本案例可以充分體現(xiàn)這點，要計算每一日對應SKU的余量，會同時涉及：
1、同時涉及兩個事實表中的日期；
2、同時涉及兩個事實表中的SKU。
那么，到底選擇哪個表作為出發(fā)點呢？
結果是殘忍的都不是。
我們必須從事實表抽取維度，而抽取通用日期維度的方式，大家都很熟悉，這里介紹抽取通用維度的方法：
Product =
DISTINCT(
UNION(
SUMMARIZE( '庫存盤點表' , [SKU] ),
SUMMARIZE( '采購入庫表' , [SKU] )
)
)這種方式，可以兼顧兩個表，且確保維度的唯一性。
BI佐羅提醒你注意
在具有主數(shù)據(jù)管理的情況下，可能會納入主數(shù)據(jù)的比對，這超越了本文的范疇，不再說明。
如何實現(xiàn) - 度量值
在有了有效結構以后，大致可以得到：
而經(jīng)過仔細分析可以發(fā)現(xiàn)，在本例中只有一個日期表是無法正常運作的，我們必須使用第二個日期表來控制計算的參考日期，如下：
形成視圖結構如下：
庫存數(shù)量和入庫數(shù)量都可以由簡單的聚合計算給出，而每次入庫所剩余的量以及庫齡則比較復雜，效果如下：
其計算給出如下：
KPI.批次余量 =
VAR _date_current = MAX( 'Calendar'[Date] )
VAR _value_current = [KPI.入庫數(shù)量]
VAR _date_next = CALCULATE( FIRSTNONBLANK( 'Calendar'[Date] , [KPI.入庫數(shù)量] ) , 'Calendar'[Date] > _date_current )
VAR _date_ref = MAX( CalendarRef[Date] )
VAR _balance = CALCULATE( LASTNONBLANKVALUE( 'Calendar'[Date] , [KPI.庫存數(shù)量] ) , 'Calendar'[Date] <= _date_ref )
VAR _inventry = FILTER( ADDCOLUMNS( ALL( 'Calendar'[Date] ) , "@Input" , [KPI.入庫數(shù)量] ) , [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.入庫數(shù)量] // 視圖表格中行值
// 具有入庫的下次日期
VAR _date_next = CALCULATE( FIRSTNONBLANK( 'Calendar'[Date] , [KPI.入庫數(shù)量] ) , 'Calendar'[Date] > _date_current )
// 參考日期
VAR _date_ref = MAX( CalendarRef[Date] )
// 按照參考日期的庫存結余
VAR _balance = CALCULATE( LASTNONBLANKVALUE( 'Calendar'[Date] , [KPI.庫存數(shù)量] ) , 'Calendar'[Date] <= _date_ref )
// 按照參考日期構建在該日期前的入庫表
VAR _inventry = FILTER( ADDCOLUMNS( ALL( 'Calendar'[Date] ) , "@Input" , [KPI.入庫數(shù)量] ) , [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 // 全被使用
)
)這就非常清楚了。
看不懂怎么辦？
看不懂或者看得懂都是正常的，因為業(yè)務邏輯本身就是如此。在熟練熟練使用 DAX 后，需要有兩個能力：
1、將業(yè)務邏輯轉換為 DAX 計算邏輯
2、為業(yè)務邏輯的計算提供合理的數(shù)據(jù)模型支持
再來看庫齡的計算就非常簡單了：
KPI.庫齡天數(shù) =
DATEDIFF( MIN( 'Calendar'[Date] ) , MAX( CalendarRef[Date] ) , DAY )按照入庫來顯示庫齡，可以快速幫助管理者鎖定必須要關注的某一批入庫，以確保沒有過有效期。
總結
管理，有高人說，是一門科學；
管理，有高人說，是一門藝術；
而彼得德魯克說，管理，既不是科學也不是藝術，而是管理者的實踐。
從實戰(zhàn)角度，在不引入任何管理復雜度的前提下，僅僅遵守先入先出的規(guī)則，并記錄好入庫表和盤點表，就可以完成管理，這是真實的——實踐。
Power BI，尤其是 DAX 的計算，既不是強調(diào)技術，也不是強調(diào)技巧，而是：
訂閱BI佐羅講授的《BI進行時》，還可觀看本案例視頻解讀。
讓數(shù)據(jù)真正成為你的力量
Create value?through?simple and?easy?with fun?by PowerBI
Excel BI?|?DAX Pro?|?DAX?權威指南?|?線下VIP學習
掃碼與PBI精英一起學習，驗證碼：data2020
PowerBI MVP 帶你學習 PowerBI
點擊“閱讀原文”，即刻開始
↙