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.
A circular dependency has been detected adding one column to a calendar table
Hello!! I have a large database of animals on a farm. I have the following measure to make the inventory that works for me however it takes a long, long time to finish. I had plans to put this formula in the PBIx calendar table adicinal column and thus have the values already calculated in a table in the hope of reducing time in inventory calculations and subsequent formulas with this data. I don't know if I'll succeed. However, when I place a column with the formula below in the calendar table with this measure, I get the error : A circular dependency has been detected: Calendar[Column]. Do you think it's possible to transform this DAX formula so that it doesn't have the circular dependency? I'd like to avoid making another inventory table, that works, although when related to the calendar table (1-1) it gives the same circular dependency error. And I´ll get a lot of difficulties in fixing all relationships.. I'm really at a blind spot with this step. Thank you Best regards INVENTÁRIO PORCAS = VAR MatrizesEntradas = CALCULATE( CALCULATE( COUNT(SOWS[Mating]), USERELATIONSHIP('Calendar'[Date], SOWS[Mating]), USERELATIONSHIP('FARM SETTINGS'[SiteId], SOWS[SiteId]) ), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), USERELATIONSHIP('Calendar'[Date], SOWS[Mating]) ) VAR MatrizesSaidas = CALCULATE( CALCULATE( DISTINCTCOUNTNOBLANK(CYCLES[ID&FARM]), NOT(ISBLANK(CYCLES[Dia Saida])), USERELATIONSHIP('Calendar'[Date], CYCLES[Dia Saida]) ), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) ) RETURN CALCULATE( MatrizesEntradas - MatrizesSaidas, FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
Inventory management (actuals and forecast combined)
Hello, I'm trying to create the following table in PowerBI. It should work as following: Jan Feb Mar Apr May Jun Jul Aug Sep Okt Nov Dec 1. Starting Inventory 20.000 25.000 35.000 25.000 35.000 40.000 45.000 55.000 45.000 25.000 45.000 35.000 2. Production 10.000 20.000 15.000 30.000 40.000 50.000 40.000 30.000 40.000 40.000 30.000 25.000 3. Sales (forecast) 5.000 10.000 25.000 20.000 35.000 45.000 30.000 40.000 60.000 20.000 40.000 30.000 4. Closing Inventory 25.000 35.000 25.000 35.000 40.000 45.000 55.000 45.000 25.000 45.000 35.000 30.000 1. Starting inventory = StockCount[StockCount] if the month is in the past and it should be equal to the previous month Closing Inventory for the future. 2. Production = Production[Production] 3. Sales (forecast) = Sales Actuals[sales(actuals)] if the month is in the past and it should be Sales Forecast[sales(forecast)] if the month is currently active or in the future. 4. Closing Inventory = Starting inventory + Production - Sales (forecast) I can't share the PBIX file here yet, since I am a fairly new member. However, I can share the file by WeTransfer: https://we.tl/t-OmfofN6WoQ
Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?
My goal: To use DAX to calculate Ending Inventory as a function of MAX(0, Beginning Inventory + Receipts - Forecast), for every day and every SKU. My Issue: I do not know how to lookup the previous day's Ending Inventory without PowerBI declaring a Circular Discrepancy. Clarification: Because Ending Inventory rounds up to 0 at the end of each day if the formula is negative, I cannot collapse my inventory calculation into a single column and subtract all forecast up to a given date and add all receipts up to a given date like this: (PseudoCode) Invalid Projected Inventory = Max( 0, LOOKUPVALUE(Current Inventory) + CALCULATE(SUM(Receipts.QTY), Receipts.Date <= Date, Receipts.Item = Item) - CALCULATE(SUM(Forecast.QTY), Forecast.Date <= Date, Forecast.Item = Item) ) The reason I cannot use that single Projected Inventory column is to consider the following example: Inventory for today (Oct 14) is 100 Total sum of Forecast thru Oct 20 is 200 Receipt of 100 will arrive Oct 21 Forecast on Oct 21, 2021 is 40 Desired Output: Projected Inventory on Oct 21 is 60: Inventory at start of Oct 21 is 0 (100 current inventory - 200 forecast, rounded up to 0) Receipt of 100 on Oct 21 - Forecast of 40 on Oct 21 = Projected Inventory of 60 Output of Invalid Formula: Projected Inventory = 0 Current Inventory of 100 - forecast thru Oct 21 of 240 + receipts of 100 = -40 Projected Inventory = Max(0, -40) = 0 Here is a screenshot of all the data I'm using in my test model (text data at the bottom), including my desired input which I can get working in Excel. And here is the same setup in PowerBI: But when I try to use DAX to calculate the Beginning and Ending inventory of each day, I get a circular discrepancy. Is there any way around this within DAX? Here are my formulas for the calculated table: Table Source: Daily Inventory = GENERATe(distinct('Date Table'[Date]), DISTINCT(Items[Item ID])) Beginning Inventory: Beginning Inventory = if('Daily Inventory'[Date]=today(), calculate(sum('Current Inventory'[Current Inventory]),filter('Current Inventory', 'Current Inventory'[Item ID]=[Item ID])), LOOKUPVALUE('Daily Inventory'[Ending Inventory],'Daily Inventory'[Item ID], [Item ID], 'Daily Inventory'[Date], [Date]-1) ) Receipts: Receipts = CALCULATE(sum(Receipts[QTY]), filter(Receipts, Receipts[Date]=earlier([Date])), filter(Receipts,Receipts[Item ID]=earlier([Item ID]))) Forecast: Forecast = CALCULATE(sum(Forecast[QTY]), filter(Forecast, Forecast[Date]=earlier([Date])), filter(Forecast,Forecast[Item ID]=earlier([Item ID]))) Ending Inventory: Ending Inventory = max([Beginning Inventory] + [Receipts] - [Forecast],0) And here are my data tables: Date Table Date 10/14/2021 10/15/2021 10/16/2021 10/17/2021 10/18/2021 Items Item ID Item Name 1 TestSKU1 2 TestSKU2 Current Inventory Item ID Current Inventory 1 100 2 100 Forecast Item ID Date QTY 1 10/14/2021 14 1 10/15/2021 12 1 10/16/2021 3 1 10/17/2021 24 1 10/18/2021 16 2 10/14/2021 43 2 10/15/2021 46 2 10/16/2021 7 2 10/17/2021 22 2 10/18/2021 32 Receipts Item ID Date QTY 1 10/17/2021 200 2 10/17/2021 200Month of Sales calculation
Hello everyone, I'm trying to find my way in solving the below request, any support is really appreciated! What I'm trying to do is set up a Measure on Power BI that calculates the Stock coverage. The general formula is the following: MoS = Stock measure / (Average Sales in Month N+1 / N+2 / N+3) The Denominator is what I can't get my head around because depending on the forecast cycle I choose and the availability of data I have, I need to tell Power BI what to consider as 3 Months sales. 2 examples below may help explain my doubts further: 1. 2022 December actuals will now need to consider Jan, Feb, Mar 2023 as Sales for the Denominator, based on the forecast that was submitted now in January. Next month I still need 2022 December MoS to take Jan, Feb and Mar Sales, but I need to recalculate based on a different Cycle (the one we will do in February that has January actuals). So I need the formula to be dynamic 2. I only have forecasts up to 2023 so as I approach the end of the year I need to take into consideration the last 3 months available (so October/November/December MoS will take the last 3 months' sales as denominator rather than the N+1/N+2/N+3) I tried approaching the problem above with a supporting table to try and set out the rules. As you can see below, at a given cycle, month and year, I should be able to establish the corresponding cycle, month and year of the Sales I need at the denominator: Cycle Month Year Key Month 1 Month 2 Month 3 PO_01_2023 Jan 2023 PO_01_2023Jan2023 PO_01_2023Feb2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023 Feb 2023 PO_01_2023Feb2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023 Mar 2023 PO_01_2023Mar2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023 Apr 2023 PO_01_2023Apr2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023 May 2023 PO_01_2023May2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023 Jun 2023 PO_01_2023Jun2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023 Jul 2023 PO_01_2023Jul2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023 Aug 2023 PO_01_2023Aug2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023 Sep 2023 PO_01_2023Sep2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 PO_01_2023 Oct 2023 PO_01_2023Oct2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 PO_01_2023 Nov 2023 PO_01_2023Nov2023 PO_01_2023Oct2023 PO_01_2023Nov2023 PO_01_2023Dec2023 Thanks again for your support! Best, Marco
Inventory Allocation
Looking for some table and report help on how to make a Inventory Allocation report to help us ship inventory to oldest order first. I have an inventory onhand report and I would like to take the onhand amount and subtract it from oldest orders until inventory goes negative. I also have an orders report that container ordered dated, order number, sku ordered and qty ordered. Currently I do this in Excel by sorting orders by oldest date and then smallest order number. Then taking inventory ohand and using the formula J2 - G3 to start off then for the rest use formual in column h as "Example H4-G4" Doing this for 100 sku's in excel takes forever and I would like to see if there is away to do this with DAX in BI. Again I have a table with Onhand Inventory and one table with Orders.
