请在文末下载附件
一、背景
在笔者以往的文章中也有先进先出的案例，可以参考(https://jiaopengzi.com/?s=%E5%85%88%E8%BF%9B%E5%85%88%E5%87%BA)。
今天我们来看一个网友提出的问题，
- 先进先出的原则，
- 入库和出库的价格都是不统一的。
- 按照出库的日期来计算先进先出的sku的毛利(fifo_profit)。
如下：
二、DAX
表间关系
我们这里按照正常业务逻辑需要有日期表：calendar、入库表：storage、出库表：sale。
关系如下：
关键的 DAX 如下，@result为需要的结果：
DEFINE
/*先进先出毛利润计算*/
VAR tableSale = ALL( 'sale' )
VAR tbaleStorage = ALL ( 'storage' )
VAR tableIn =
/*计算每条入库记录的累计入库：@cumIn*/
ADDCOLUMNS (
tbaleStorage,
"@cumIn",
VAR dateIn = [date]
VAR skuIn = [sku]
VAR tableInX =
FILTER ( tbaleStorage, [sku] = skuIn && [date] <= dateIn )
RETURN
SUMX ( tableInX, [units] )
)
VAR tableOut =
/*计算每条出库记录的累计出库：@cumOut*/
ADDCOLUMNS (
tableSale,
"@cumOut",
VAR dateOut = [date]
VAR skuOut = [sku]
VAR tableOutX =
FILTER ( tableSale, [sku] = skuOut && [date] <= dateOut )
RETURN
SUMX ( tableOutX, [units] )
)
VAR tableResult =
/*掐头去尾找到出库和入库的对应关系，计算出销售金额和入库成本之间的差异计算出毛利润*/
ADDCOLUMNS (
tableOut,
"@result",
VAR dateOut = [date]
VAR cumOut = [@cumOut]
VAR skuAc = [sku]
VAR tableOutPre =
FILTER ( tableOut, [sku] = skuAc && [date] < dateOut ) //前一个@累计出库
VAR preMaxCumOut =
MAXX ( tableOutPre, [@cumOut] )
VAR tableInStartEnd = // 先进先出规则下，出库数据对应到入库数据
FILTER (
tableIn,
[sku] = skuAc && [date] <= dateOut && [@cumIn] >= preMaxCumOut
)
VAR tableInOut =// 计算每次入库记录对应到出库的数量
ADDCOLUMNS (
tableInStartEnd,
"@unitOut",
VAR dateMin =
MINX ( tableInStartEnd, [date] )
RETURN
SWITCH (
TRUE (),
cumOut > [@cumIn] && [date] = dateMin, [@cumIn] - preMaxCumOut,
cumOut > [@cumIn], [units],
cumOut - [@cumIn] + [units]
)
)
VAR tableOkCum = FILTER ( tableInOut, [@cumIn] >= cumOut ) // 每条记录 满足累计入库大于等于出库
VAR dateMin =MINX ( tableOkCum, [date] )
VAR tableOkDate = FILTER ( tableInOut, [date] <= dateMin ) // 每条记录 日期小于等于 dateMin
VAR cost = SUMX ( tableOkDate, [price] * [@unitOut] ) // 计算出库成本
VAR profit = [price] * [units] - cost // 计算毛利
RETURN
profit
)
EVALUATE
tableResult
三、总结
- 先进先出的主要方法是"掐头去尾"，找到出库信息对应到的入库信息。
- 按照每行记录计算出库和入库数量累计值，再通过迭代函数计算对应的入库成本和出库销售额，从而通过差异计算出毛利。
- 由于业务嵌套层数比较多，可以利用 Dax Studio 调试，多使用 CONCATENATEX。
请关注
全网同名搜索 焦棚子
如果对你有帮助，请 点赞、关注、三连 支持一下，这是我们更新的动力。
我们承接 Power BI 相关业务：培训、报告制作与部署、咨询服务等。
by焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论