Fifo Stock cost - Multiple Tables & Products & Same dates

pwagma — Tue, 16 Mar 2021 17:33:01 GMT

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

Re: Fifo Stock cost - Multiple Tables & Products & Same dates

Anonymous — Fri, 02 Apr 2021 11:12:14 GMT

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

Can you please provide the sample data and expected output?

topic Fifo Stock cost - Multiple Tables & Products & Same dates in DAX Commands and Tips

Fifo Stock cost - Multiple Tables & Products & Same dates

Re: Fifo Stock cost - Multiple Tables & Products & Same dates