127_Power Pivot&Power BI DAX计算订单商品在库时间(延伸订单仓储费用)

---

一、背景

前面已经写过一个[先进先出的库龄案例]，在业务发生又有这样一个需求：先进先出前提，需要按照订单计算每个商品在库时间，也就是说计算一个订单中的商品从入库到出库的时间。

当已知商品的每天库存费用是多少时，可计算出每个订单的仓储费用，用在仓储的内部结算中。

关键点在于计算出单位库存在库天数，同时也引出了本文的案例（如需计算仓储费用加上一个商品单价即可，本文不单独说明）。

案例如下：

图1

二、数据源

图2

1、本案例中暂时没有用到日期表，养成良好的建模习惯也是很有必要的。

2、单位库存在库天数的计算逻辑见《图1》。

三、上DAX

单位库存在库天数:=
VAR T0=ADDCOLUMNS('入库',"@累计入库",
	VAR dateruku='入库'[入库日期]
	VAR skuruku='入库'[SKU]
	VAR T01=FILTER('入库','入库'[SKU]=skuruku&&'入库'[入库日期]<=dateruku)
	RETURN
	SUMX(T01,[入库数量])
	)

VAR T1=ADDCOLUMNS('出库',"@累计出库",
	VAR datechuku='出库'[出库日期]
	VAR skuchuku='出库'[SKU]
	VAR T11=FILTER(ALL('出库'),'出库'[SKU]=skuchuku&&'出库'[出库日期]<=datechuku)
	RETURN
	SUMX(T11,[出库数量])
	)

VAR T2=ADDCOLUMNS(ALL('出库'),"@累计出库",
	VAR datechuku_all='出库'[出库日期]
	VAR skuchuku_all='出库'[SKU]
	VAR T21=FILTER(ALL('出库'),'出库'[SKU]=skuchuku_all&&'出库'[出库日期]<=datechuku_all)
	RETURN
	SUMX(T21,[出库数量])
	)
VAR T3=ADDCOLUMNS(T1,"@result",
	VAR dateck=[出库日期]
	VAR ljck=[@累计出库]
	VAR skuac=[SKU]
	VAR T31=FILTER(T2,[SKU]=skuac&&[出库日期]<dateck)//前一个@累计出库
	VAR MAXCK=MAXX(T31,[@累计出库])
	VAR T32=FILTER(T0,[SKU]=skuac&&[入库日期]<=dateck&&[@累计入库]>=MAXCK)
	VAR T33=ADDCOLUMNS(T32,"@库存",
		VAR datemin=MINX(T32,[入库日期]) 
		RETURN
		IF([入库日期]=datemin,[@累计入库]-MAXCK,[入库数量])
		)
	VAR T34=FILTER(T33,[@累计入库]-ljck>=0)
	VAR datemax=MINX(T34,[入库日期])//累计@库存刚好满足订单需求的最大日期
	VAR T35=FILTER(T33,[入库日期]<=datemax)
	VAR T36=ADDCOLUMNS(T35,"@天数",DATEDIFF([入库日期],dateck,DAY)+1,"@数量",IF([@累计入库]-ljck>0,[@库存]-([@累计入库]-ljck),[@库存]))
	RETURN
	SUMX(T36,[@天数]*[@数量])
)
RETURN
SUMX(T3,[@result])

四、总结

1、案例本身没有太多复杂的DAX应用；

2、主要是T1和T2中ALL的应用，在T3的T31的外部筛选上下文的去除体现，业务逻辑转换上下文重点；

3、T3中涉及到多层的嵌套特别是T36，这个需要有空间想象力，可以借助Dax Studio去分步理解；

4、另外一个关于临时列“@”的命名要求，养成良好的DAX书写习惯。详见如下链接SQLBI文章。