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-valuati...
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.