? 优质资源分享 ?
| 学习路线指引（点击解锁） | 知识定位 | 人群定位 |
|---|---|---|
| ? Python实战微信订餐小程序 ? | 进阶级 | 本课程是python flask+微信小程序的完美结合，从项目搭建到腾讯云部署上线，打造一个全栈订餐系统。 |
| ?Python量化交易实战? | 入门级 | 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 |
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 焦棚子