部落格:www。jiaopengzi。com
一、背景
雙十二回來後遇到一個
比較有意思
的計算銷售額和利潤率的需求(見下文說明)。
先看下效果。
結果
說明:
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為例:
總成本=201100+201100+211*200=82400
總數量=100+100+200=400
成本單價=82400/400=206
二、資料來源
1、出庫
2、期初庫存
3、入庫
4、價格表
5、關係
三、上度量值
1、訂單數量
訂單數量:=DISTINCTCOUNT(‘出庫’[訂單id])
2、出庫
出庫:=SUM(‘出庫’[數量])
3、期初
期初:=SUM(‘期初庫存’[數量])
4、入庫
入庫:=SUM(‘入庫’[數量])
5、銷售價
銷售價:=SUM(‘價格表’[售價])
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、如有先進先出的需求,體現在批次管理即可,本案例不是先進先出的案例。
by焦棚子