Forum Discussion

Martin_MK's avatar
Martin_MK
Frequent Visitor
6 years ago

FIFO margin calculation

Hi, I need to create a margin calculation for stock using the FIFO way.

I have found some example, but it's giving me a negative FIFO calculation.
https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

I have spent already a week trying to solve this and have no idea where is the problem.

 

If somebody likes solving a puzzle and would give me some help I would be appreciated.

 

Thanks.

1 Reply

  • Martin_MK's avatar
    Martin_MK
    Frequent Visitor
    6 years ago

    Here is the FIFO column calculation

     

    FIFO column = 
VAR myCurrentSell = 'Table1'[Cumulative Sell]
VAR myLastSell = 'Table1'[Previous Cumulative Sell]
VAR mySymbol = 'Table1'[Symbol]
VAR myCumulativeBuy = 'Table1'[Cumulative Buy]
VAR myLastCumulativeBuy = 'Table1'[Previous Cumulative Buy]
VAR FIFOFilterTable =
    FILTER (
        'Table1',
        'Table1'[Symbol] = mySymbol
            && 'Table1'[type] = "Buy"
            && ( ( 'Table1'[Cumulative Buy] >= myLastSell
            && 'Table1'[Cumulative Buy] < myCurrentSell )
            || 'Table1'[Cumulative Buy] >= myCurrentSell
            && 'Table1'[Previous Cumulative Buy] < myCurrentSell
            || 'Table1'[Previous Cumulative Buy]  > myLastCumulativeBuy
            && 'Table1'[Cumulative Buy] < myLastCumulativeBuy )
    )
VAR FilteredFIFOTable =
    ADDCOLUMNS (
        FIFOFilterTable,
        "New Value", SWITCH (
            TRUE (),
            'Table1'[Cumulative Buy] > myLastSell
                && 'Table1'[Previous Cumulative Buy] < myLastSell, 'Table1'[Units]
                - ( myLastSell - 'Table1'[Previous Cumulative Buy] ),
            'Table1'[Cumulative Buy] < myCurrentSell, 'Table1'[Units],
            -- ELSE --
            'Table1'[Units]
                - ( 'Table1'[Cumulative Buy] - myCurrentSell )
        )
    )
VAR Result =
    Table1[Total value] - SUMX ( FilteredFIFOTable, [New Value] * 'Table1'[value per unit] )
      
RETURN
    IF ( 'Table1'[type] = "Sale", Result )

     

     

    and in the attachment is the screenshot with negative value;

     

    All is good for the "AAA" item, the problem is with "mmm" one.