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

焦棚子 2020-03-0517:02:0433,468字数 1653阅读5分30秒阅读模式

焦棚子的文章目录

请在文末下载附件

 

一、背景

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

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

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

案例如下:

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

二、数据源

127_Power Pivot&Power BI DAX计算订单商品在库时间(延伸订单仓储费用)
图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文章。

Naming temporary columns in DAX - SQLBI​www.sqlbi.com

 

by 焦棚子

焦棚子的文章目录

请点击【立即购买】或者【升级VIP】获得案例附件。

隐藏内容需要支付:¥5
立即购买 升级VIP

焦棚子
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接!
  • 《127_Power Pivot&Power BI DAX计算订单商品在库时间(延伸订单仓储费用)》链接:https://jiaopengzi.com/469.html