Dax logic not working
Hello again, its been a long month i am tring to find the problem in my code but i have no idea of whats going on. If anyone can help me with this it would be so good. I have two dashboards, both have the same code, the thing is, one is working with an excel file as source and the other has an internal source, i dont know if thats the problem root but thats the only difference between them. The dashboards: not working (ControleDaProducaoMaisLeve&BALSendDistribui.pbix) using excel source Working well (Sum interval considering many filters11101.pbix) own source the logic is, the stock might be distribuited to the orders in a way that it priorizes the orders with the lowest value (number) and if the item in the order has a value bigger than the remmaing stock (after being distributed to the orders that the quantity needed is <= to the stock) then, this orders will not be considered and other order with the item will be tested.. Thankyou all.
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
Share Valuation using FIFO method
Hello - I am trying to implement FIFO in share valuation. I was able to get it in excel, but not able to get the calculation in Power BI. I am including the sample data (input and output) as was done in excel - gdrive link (https://drive.google.com/open?id=1vsc8R5MnGy2MVmqxUI7wgHssl6K6wjgM). The blue header columns in FINAL RESULT are the required output - I need help in creating these columns in Power BI - (a) CAPITAL GAIN (b) STOCK IN HAND © CLOSING VALUE Also is the link for the PBX file (https://drive.google.com/open?id=1irJ2vOyV50WdVyYU45YQwA12rkqUNojm), where I am not able to fix the Calculated Column - “Cost Basis with FIFO”. Below is the DAX calculation for FIFO method that I am using - which is not working as per FIFO as it is not picking RATE (the multiplication factor) of the BUY QTY - rather it is picking the current row (SALE RATE), which is not the expected behavior of FIFO. Cost Basis with FIFO = VAR myUnits=[Qty.] VAR PreviousBuys= FILTER(TransactionData, [IsIn]=EARLIER([IsIn])&& [Sauda Date]<EARLIER([Sauda Date])&& ([Net Qty.] > 0) ) VAR PreviousSales= SUMX( FILTER(TransactionData, [IsIn]=EARLIER([IsIn])&& [Sauda Date]<EARLIER([Sauda Date])&& ([Net Qty.] < 0) ), [Qty.] ) VAR PreviousBuysBalance= ADDCOLUMNS( ADDCOLUMNS( PreviousBuys, “Cumulative”, SUMX( FILTER(PreviousBuys, ([Sauda Date]<=EARLIER([Sauda Date])) ), [Qty.]) ), “Balance Left”, [Qty.]-IF([Cumulative]<PreviousSales, [Qty.], VAR PreviousCumulative=[Cumulative]-[Qty.] RETURN IF(PreviousSales>PreviousCumulative,PreviousSales-PreviousCumulative) ) ) VAR CostUsed= ADDCOLUMNS ( ADDCOLUMNS ( PreviousBuysBalance, “MyCumulatives”, SUMX ( FILTER(PreviousBuysBalance, ([Sauda Date]<=EARLIER([Sauda Date])) ), [Balance Left]) ),“Balance Used”, IF( [MyCumulatives]<myUnits,[MyCumulatives], VAR PreviousCumulatives=[MyCumulatives]-[Balance Left] RETURN IF(myUnits>PreviousCumulatives,myUnits-PreviousCumulatives) ) ) RETURN IF([Net Qty.] < 0, ([Qty.][Rate])-SUMX(CostUsed,([Balance Used][Rate])))