182_Power BI 使用 DAX 按照先进先出原则计算毛利润

焦棚子 2023-07-0317:51:2741,4542字数 1876阅读6分15秒阅读模式

焦棚子的文章目录

请在文末下载附件

 

一、背景

在笔者以往的文章中也有先进先出的案例,可以参考(https://jiaopengzi.com/?s=%E5%85%88%E8%BF%9B%E5%85%88%E5%87%BA)。

今天我们来看一个网友提出的问题,

  • 先进先出的原则,
  • 入库和出库的价格都是不统一的。
  • 按照出库的日期来计算先进先出的sku的毛利(fifo_profit)。

如下:

182_Power BI 使用 DAX 按照先进先出原则计算毛利润

 

二、DAX

表间关系

我们这里按照正常业务逻辑需要有日期表:calendar、入库表:storage、出库表:sale

关系如下:

182_Power BI 使用 DAX 按照先进先出原则计算毛利润

 

关键的 DAX 如下,@result为需要的结果:

182_Power BI 使用 DAX 按照先进先出原则计算毛利润

 

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

 

三、总结

  1. 先进先出的主要方法是"掐头去尾",找到出库信息对应到的入库信息。
  2. 按照每行记录计算出库和入库数量累计值,再通过迭代函数计算对应的入库成本和出库销售额,从而通过差异计算出毛利。
  3. 由于业务嵌套层数比较多,可以利用 Dax Studio 调试,多使用 CONCATENATEX

 


请关注

全网同名搜索 焦棚子

如果对你有帮助,请 点赞关注三连 支持一下,这是我们更新的动力。

我们承接 Power BI 相关业务:培训、报告制作与部署、咨询服务等。

182_Power BI 使用 DAX 按照先进先出原则计算毛利润

 

 

by焦棚子

焦棚子的文章目录

 

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

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

焦棚子
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接!
  • 《182_Power BI 使用 DAX 按照先进先出原则计算毛利润》链接:https://jiaopengzi.com/3026.html