cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwagma
Regular Visitor

Fifo Stock cost - Multiple Tables & Products & Same dates

Hello

I have been reading a lot of articles about FIFO stock valuation. 
I have mainly followed the following one: https://radacad.com/dax-inventory-or-stock-valuation-using-fifo.

 

The particular situation I have is that I have 2 different tables. One for the sales operations and one for the buy operations.

To solve the only 1 operation per day limit, I used an index column in each table.

 

No the problem is where I calculate the FIFO column. When I declare the variable it is not accepted and I have different errors.

 

FIFO = 
VAR myCurrentSell = Hoja1[Cantidad Acumulada]
VAR myLastSell = Hoja1[Cantidad acumulada anterior]
VAR mySymbol = Hoja1[Artículo]
VAR myCumulativeBuy = FIRSTNONBLANKVALUE(Transferencias[Kg Acumulados],FILTER(Transferencias, mySymbol))
VAR myLastCumulativeBuy = FIRSTNONBLANKVALUE(Transferencias[Kg Acumulados Anteriores],FILTER(Transferencias, mySymbol))
VAR FIFOFilterTable =
    FILTER (
        Transferencias,
        Transferencias[Producto] = mySymbol
            && ( ( Transferencias[Kg Acumulados] >= myLastSell
            && Transferencias[Kg Acumulados] < myCurrentSell )
            || Transferencias[Kg Acumulados] >= myCurrentSell
            && Transferencias[Kg Acumulados Anteriores] < myCurrentSell
            || Transferencias[Kg Acumulados Anteriores]  > myLastCumulativeBuy
            && Transferencias[Kg Acumulados] < myLastCumulativeBuy )
    )
VAR FilteredFIFOTable =
    ADDCOLUMNS (
        FIFOFilterTable,
        "New Value", SWITCH (
            TRUE (),
            Transferencias[Kg Acumulados] > myLastSell
                && Transferencias[Kg Acumulados Anteriores] < myLastSell, Hoja1[cantidad]
                - ( myLastSell - Transferencias[Kg Acumulados Anteriores] ),
            Transferencias[Kg Acumulados] < myCurrentSell, Hoja1[cantidad],
            -- ELSE --
            Hoja1[cantidad]
                - ( Transferencias[Kg Acumulados] - myCurrentSell )
        )
    )
RETURN
    Related(Transferencias[Costo Total])
        - SUMX ( FilteredFIFOTable, [New Value] * related(Transferencias[Costo Unitario])
)

The column fifo is made on the "Sales" table. (Of course I need to know the cost of each sale).

When I declare the variable mycumulativebuy and mypreviouscumulativebuy, it doesn't recognize the relationship with the other table. I tried to use other ways to declare it (hence the function that you can see in the code) but the error it comes is "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

I appreciate your help

Thanks

pablo

 

 

1 REPLY 1
TarunSharma
Skilled Sharer
Skilled Sharer

Hello @pwagma ,
You may try
myCumulativeBuy = 
Firstnonblankvalue(Transferencias[kg acumulados], Filter(Transferencias, Transferencias = mysymbol))

Can you please provide the sample data and expected output?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors