双十二回来后遇到一个比较有意思的计算销售额和利润率的需求(见下文说明)。
先看下效果。
结果
说明:
1、订单表中不体现销售价格,如上图《出库》。
2、销售价格单独管理,如上图《价格表》;
说明:价格是不断调整的,以上图红色填充的sku1为例,
即
2018/12/1至2018/12/10销售价:592,
2018/12/11至2018/12/13销售价:581,
2018/12/14后销售价:500。
3、库存每月1号给出一个当月期初库存如上图《期初库存》,包含成本价和数量
4、入库商品的成本价是浮动的如上图中黄色填充sku1,
即
2018/12/1期初成本价:201,
2018/12/10入库成本价:201,
2018/12/11入库成本价:211,
约定:成本价按照加权平均求得
以sku1为例:
总成本=201*100+201*100+211*200=82400
总数量=100+100+200=400
成本单价=82400/400=206
1、出库
2、期初库存
3、入库
4、价格表
5、关系
1、订单数量
订单数量:=DISTINCTCOUNT('出库'[订单id])
2、出库
3、期初
4、入库
5、销售价
6、库存
库存:= VAR TF1 = IF ( MAX ( '出库'[日期] ) < MAX ( '日期表'[date] ), FALSE (), TRUE () ) VAR TF2 = ISFILTERED ( '日期表'[date] ) RETURN SWITCH ( TRUE (), TF1 && TF2, CALCULATE ( '度量值'[期初], DATESMTD ( '日期表'[date] ) ) + CALCULATE ( '度量值'[入库], DATESMTD ( '日期表'[date] ) ) - CALCULATE ( '度量值'[出库], DATESMTD ( '日期表'[date] ) ), TF2 = FALSE (), CALCULATE ( '度量值'[期初], DATESMTD ( '日期表'[date] ) ) + CALCULATE ( '度量值'[入库], DATESMTD ( '日期表'[date] ) ) - CALCULATE ( '度量值'[出库], DATESMTD ( '日期表'[date] ) ) )
7、销售额
销售额:= SUMX ( ADDCOLUMNS ( '出库', "单价", CALCULATE ( [销售价], FILTER ( ALL ( '价格表' ), '价格表'[日期] <= '出库'[日期] && '价格表'[日期] >= TOPN ( 1, CALCULATETABLE ( VALUES ( '价格表'[日期] ), FILTER ( ALL ( '价格表' ), '价格表'[日期] <= '出库'[日期] && '价格表'[sku] = '出库'[sku] ) ), '价格表'[日期], DESC ) && '价格表'[sku] = '出库'[sku] ) ) ), '出库'[数量] * [单价] )
8、成本
成本:= VAR DS = STARTOFMONTH ( '出库'[日期] ) VAR DE = ENDOFMONTH ( '出库'[日期] ) VAR T1 = DATESBETWEEN ( '日期表'[date], DS, DE ) VAR T2 = SUMMARIZE ( sku, sku[sku], "CC", DIVIDE ( SUMX ( CALCULATETABLE ( '期初库存', DS, ALL ( '日期表'[date] ) ), '期初库存'[成本价] * '期初库存'[数量] ) + SUMX ( CALCULATETABLE ( '入库', T1, ALL ( '日期表'[date] ) ), '入库'[成本价] * '入库'[数量] ), SUMX ( CALCULATETABLE ( '期初库存', DS, ALL ( '日期表'[date] ) ), '期初库存'[数量] ) + SUMX ( CALCULATETABLE ( '入库', T1, ALL ( '日期表'[date] ) ), '入库'[数量] ) ) ) RETURN SUMX ( ADDCOLUMNS ( '出库', "成本单价", SUMX ( FILTER ( T2, [sku] = '出库'[sku] ), [CC] ) ), [成本单价] * [数量] )
9、利润率
利润率:=DIVIDE('度量值'[销售额]-'度量值'[成本],'度量值'[销售额])
1、根据业务创建相关度量值上下文;
2、本案例中难点主要在度量值6、销售额和7、成本;
3、销售额中价格巧妙使用TOPN,成本中对加权求平均dax的转换。
4、如有先进先出的需求,体现在批次管理即可,本案例不是先进先出的案例。