Help with FIFO logic with two Tables in DirectQuery
Hello, I'm struggling with creating a way to measure and/or visualize if a Load Unit that has been delivered to us, was also unloaded first. Deliveries have Priority 1-3 so what we want is, that the first delivery arrived is also the one that is unloaded first. I'm stuck with working in DQ and have to tables to do this- Transports Table and Unloading Table Transports Table has the Columns LoadUnitId, Priority and ActualArrival Timestamp. Unloading Table has the column StarTime Unloading. Something like this: LoadUnitId Priority ActualArrival StartTime Unloading 1234 1 01.01.2025 08:00 01.01.2025 08:15 4321 2 01.01.2025 07:35 01.01.2025 07:56 2134 1 01.01.2025 07:15 01.01.2025 08:19 What kind of expression can be used to check if a Load Unit that has the same priority, was delivered before another one, but unloaded later. I've been thinking of using Ranks but DirectQuery is a bit tricky and won't allow that. Maybe Flags would do the trick but I'm unsure how to use them in this case exactly. Any help would be highly appreciated. Thank you very much 🙂
Dax measure for calculating remaining quantity of inventory, of a selected date
Hello, I have encountered a problem, trying to write a measure. This measure should account for price per unit in Received and Warehouse. Warehouse is simple because both tables Received and Issued has Warehouse. Price per unit is different, the issued item has no price per unit, so the logic should be that the first items that arrive in the warehause are the first items issued. For example: I have received 5 item a worth 0.5 per unit and 6 item a worth 0.6 per unit, then issue 7 items, so the first ones to go are items worth 0.5 per unit, and because there are 2 more items to issue i have to take them from the newer batch that cost 0.6 per unit. So after all calculations i should be left with 4 items worth at 0.6 per unit. An example with dummby data: I have 3 tables in power bi: 1st table is Received Date Item Warehause PricePerUnit QTY(Received) 2024-05-15 12a gs1 3.5 3 2024-05-17 12a gs1 3.8 7 2024-06-01 12a gs2 4.5 9 2024-06-05 12a gs1 3.5 3 2nd table is Issued: Date Item Warehause QTY(Issued) 2024-05-18 12a gs1 5 2024-06-03 12a gs2 8 2024-06-10 12a gs1 6 3rd table is a simple DateTable with Date So if i would select that i want to see Remaining quantities from 2024-05-19 i would get: Item Warehause PricePerUnit RemainingQTY(New Measure) 12a gs1 3.5 0 12a gs1 3.8 5 And if I would want to see Remaining quantities from 2024-06-18 i would get: Item Warehause PricePerUnit RemainingQTY(New Measure) 12a gs1 3.5 2 12a gs1 3.8 0 12a gs2 4.5 1 Any help with this measure would be highly appreciated.
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])))