155_模型_Power BI & Power Pivot 進銷存之安全庫存

一、背景

談進銷存的概念時，我們也需要提及另外一個概念：安全庫存。

庫存週轉在理想的狀態下是做到零庫存，但是在內部的資金佔用及到貨週期不確定和外部客戶的現貨率滿足等諸多情況平衡下，想做到零庫存幾乎不可能。在這種平衡狀態就有了安全庫存；以上為筆者在平常的生產生活中感知。

在這裡我們首先引入安全庫存的數學公式：

說明

分佈按照理想狀態的標準正太分佈，安全庫存僅作為參考點，實際生產中需要加入業務的理解，進行調整。

數學公式轉變為 DAX 公式：SS = z * SQRT ( σd ^ 2 * μL + σL ^ 2 * μd ^ 2 )

SS：安全庫存（Safe Stock）。

z : 服務水平係數，服務水平（現貨率）為 0.95，在標準正太分佈下查表可得，對應的 z = 1.65。

L : 採購提前期，本案例中對應入庫間隔天數。

μL: 平均提前期。

σL: 提前期 L 的標準差。

d : 日出庫數量。

μd: 平均日出庫數量。

σd: 日出庫量 d 的標準差。

標準差公式使用：STDEVX.S

更多安全庫存的資訊參考：

https://baike.baidu.com/item/安全庫存

Power BI 實際效果連結：

https://demo.jiaopengzi.com/pbi/155-full.html

二、DAX模型

1、資料表及關係

維度表：01_Calendar； T01_門店表； T00_產品表

事實表：T04_訂單主表； T05_訂單子表

度量值：00_Measure

輔助表：04_RefreshTime

計算表：02_Row

具體欄位和關係如下圖：

詳細資料欄位介紹見：

https://jiaopengzi.com/1435.html

2、度量值

度量值一覽圖

接下來我們把幾個關鍵的度量值放上來。

0001_產品數量_入庫

0001_產品數量_入庫 = 
SUM ( 'T03_入庫資訊表'[F_02_入庫產品數量] )

0002_產品數量_出庫

注意出庫是需要按照訂單主表裡面的送貨日期來計算，所以需要使用 USERELATIONSHIP 啟用關係。

0002_產品數量_出庫 = 
CALCULATE (
    SUM ( 'T05_訂單子表'[F_05_產品銷售數量] ),
    USERELATIONSHIP ( 'T04_訂單主表'[F_04_送貨日期], '01_Calendar'[C01_Dates] )
)

0003_產品數量_庫存_期末

關於庫存的計算，給大家提供一個思路。庫存就是當前時間點的歷史累計入庫與歷史累計出庫的差異。我們用一張圖來表示。

在這樣的思路下，我們也就很好使用 DAX 寫進銷存裡面的庫存的度量值了。

當然庫存又會根據不同的時間點的狀態分為期初庫存和期末庫存，關於期初和期末智慧庫存的度量值可以在我的課程裡面學習。

https://jiaopengzi.com/video/video-101?rkvid=100

0003_產品數量_庫存_期末 = 
VAR DATE_START0 =
    CALCULATE ( FIRSTDATE ( '01_Calendar'[C01_Dates] ), ALL ( '01_Calendar' ) )
VAR DATE_END0 =
    LASTDATE ( '01_Calendar'[C01_Dates] )
VAR DATE_TABLE0 =
    DATESBETWEEN ( '01_Calendar'[C01_Dates], DATE_START0, DATE_END0 )
VAR IN0 =
    CALCULATE ( [0001_產品數量_入庫], DATE_TABLE0 )
VAR OUT0 =
    CALCULATE ( [0002_產品數量_出庫], DATE_TABLE0 )
VAR DATE_START_ABS0 =
    CALCULATE ( LASTDATE ( 'T04_訂單主表'[F_04_送貨日期] ), ALL () ) //相容顯示到事實表最後日期。
RETURN
    IN0 - OUT0
//IF(DATE_START_ABS0>=DATE_END0,IN0 - OUT0,BLANK())

0004_產品數量_安全庫存

本案例裡面最重要的度量值；如果對中間過程理解有困難的，可以使用我們為大家準備的 Excel 輔助校驗表來體會。

具體使用案例附件 Power BI 檔案中的第二頁《P2校驗資料》匯出對應的資料，在附件 Excel 《安全庫存步驟分解》中分佈體會。

Excel 裡面有公式方便理解。

0004_產品數量_安全庫存 = 
/*
分佈按照理想狀態的標準正太分佈，安全庫存僅作為參考點，實際生產中需要加入業務的理解，進行調整。
SS = z * SQRT ( σd ^ 2 * μL + σL ^ 2 * μd ^ 2 )
SS：安全庫存（Safe Stock）。
z : 服務水平係數，服務水平（現貨率）為 0.95，在標準正太分佈下對應的 z = 1.65。
L : 採購提前期，本案例中對應入庫間隔天數。
μL: 平均提前期。
σL: 提前期 L 的標準差。
d : 日出庫數量。
μd: 平均日出庫數量。
σd: 日出庫量 d 的標準差。
*/

VAR N = [02_ROW 值]//預設30天
VAR DATE_END0 =
    LASTDATE ( '01_Calendar'[C01_Dates] )
VAR DATE_START0 =
    DATEADD ( DATE_END0, - N + 1, DAY ) // 注意不包含邊界。
VAR DATE_Table0 =
    DATESBETWEEN ( '01_Calendar'[C01_Dates], DATE_START0, DATE_END0 )
VAR T01 =
    CALCULATETABLE ( 'T03_入庫資訊表', DATE_Table0 )
VAR T02 =
    SUMMARIZE (
        T01,
        '01_Calendar'[C01_Dates],
        'T00_產品表'[F_01_產品編號],
        'T01_門店表'[F_01_門店編號],
        "@入庫", [0001_產品數量_入庫]
    )
VAR T03 =
    SUMMARIZE (
        T02,
        [F_01_產品編號],
        [F_01_門店編號],
        "@μL",
            VAR P0 = [F_01_產品編號]
            VAR M0 = [F_01_門店編號]
            VAR T_FILTER =
                FILTER ( T02, [F_01_產品編號] = P0 && [F_01_門店編號] = M0 )
            VAR DATE_MAX =
                MAXX ( T_FILTER, [C01_Dates] )
            VAR DATE_MIN =
                MINX ( T_FILTER, [C01_Dates] )
            VAR DAY_DIFF =
                DATEDIFF ( DATE_MIN, DATE_MAX, DAY )
            VAR T_ROWS =
                COUNTROWS ( T_FILTER ) - 1 // 注意去除邊界減 1 。
            RETURN
                DIVIDE ( DAY_DIFF, T_ROWS, 0 ),
        "@σL",
            VAR P0 = [F_01_產品編號]
            VAR M0 = [F_01_門店編號]
            VAR T_FILTER =
                FILTER ( T02, [F_01_產品編號] = P0 && [F_01_門店編號] = M0 )
            VAR T_INDEX =
                SUBSTITUTEWITHINDEX (
                    ADDCOLUMNS ( T_FILTER, "@date", [C01_Dates] ),
                    "@INDEX", T_FILTER,
                    [C01_Dates], ASC
                )
            VAR T_INDEX_N =
                ADDCOLUMNS (
                    T_INDEX,
                    "@N",
                        VAR INDEX0 = [@INDEX]
                        VAR T_1 =
                            FILTER ( T_INDEX, [@INDEX] = INDEX0 - 1 )
                        VAR DATE_1 =
                            MAXX ( T_1, [@date] )
                        VAR N0 =
                            DATEDIFF ( DATE_1, [@date], DAY )
                        RETURN
                            N0
                )
            VAR T_INDEX_N_NOT_BLANK =
                FILTER ( T_INDEX_N, [@N] <> BLANK () )
            RETURN
                IFERROR ( STDEVX.S ( T_INDEX_N_NOT_BLANK, [@N] ), 0 )
    )
VAR T11 =
    CALCULATETABLE (
        'T05_訂單子表',
        DATE_Table0,
        USERELATIONSHIP ( 'T04_訂單主表'[F_04_送貨日期], '01_Calendar'[C01_Dates] )
    ) //送貨日期需要啟用關係。
VAR T12 =
    SUMMARIZE (
        T11,
        '01_Calendar'[C01_Dates],
        'T00_產品表'[F_01_產品編號],
        'T01_門店表'[F_01_門店編號],
        "@出庫", [0002_產品數量_出庫]
    )
VAR T13 =
    SUMMARIZE (
        T12,
        [F_01_產品編號],
        [F_01_門店編號],
        "@μd",
            VAR P0 = [F_01_產品編號]
            VAR M0 = [F_01_門店編號]
            VAR T_FILTER =
                FILTER ( T12, [F_01_產品編號] = P0 && [F_01_門店編號] = M0 )
            VAR AVG0 =
                AVERAGEX ( T_FILTER, [@出庫] )
            RETURN
                AVG0,
        "@σd",
            VAR P0 = [F_01_產品編號]
            VAR M0 = [F_01_門店編號]
            VAR T_FILTER =
                FILTER ( T12, [F_01_產品編號] = P0 && [F_01_門店編號] = M0 )
            VAR STDE0 =
                IFERROR ( STDEVX.S ( T_FILTER, [@出庫] ), 0 )
            RETURN
                STDE0
    )
VAR T =
    NATURALINNERJOIN ( T03, T13 )
VAR z = [0010_產品數量_安全庫存_z]
VAR SS =
    //SS= z * SQRT( σd ^ 2 * μL + σL ^ 2 * μd ^ 2 )
    SUMX ( T, z * SQRT ( [@σd] ^ 2 * [@μL] + [@σL] ^ 2 * [@μd] ^ 2 )
    )
RETURN
    SS

三、總結

1、安全庫存作為庫存管理的一種重要參考，本案例中使用的是標準正太分佈的理性情況，所以在實際的生產生活中還要根據業務特點再調整。

2、本案例給出了一種統計學意義下安全庫存使用 DAX 計算思路。

3、另外關於庫存計算的理解，歷史累計對應到 DAX 中也就很好寫了，首先找到業務的時間起點，因為時間起點不會變，也可以直接寫死，再找到當前的時間點，結合 DATESBETWEEN 就能構建我們需要的各種業務時間描述了。

by 焦棚子