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
