Forum Discussion
Stock quantity counting help
Hi,
I have the following problem. My matrix countains products, currentstock at time, and current price at the time.
As you can see, this product started with 40 stock, and gets lower with sales. It works great. But if i add a date filter, and lets say i give it 2022.06.17 as starting date it starts counting from 0. The correct starting stock should be 37. I dont have the current stock quantity in tables, i have to calculate it from sales and delivery records.
I tried this measure but its not working.
The expected result would be the last stock quantity at the time. Starting from 40 to 28, if i close the row it should show 28 as the current stock.
I would like to ask for same help how to fix this problem.
Thank you.
Hi, i found the solotion. In the item table for each row i created a new cumulative sum column. After that, i created a measure that gets me the max ID for each date. After that i know the row with the quantity i am looking for.
11 Replies
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hello,
Thanks for the reply.
Expected Outcome:
At each documentheader fulfillmentdate, i would like to get the actual stock quantity, based on document movement.
Filtering: 2022.01.01 - 2022.12.31
2022.may 11. --> 40 --> Started at 0, but got a delivery +40
2022.jun.2 --> 40 --> Started at 40, followed by - 20 + 20, the outcome is 40.
2022.jun 9 --> 39 --> Started at 40, sold 1, outcome is 39
2022.jun 10 --> 38 --> Started at 39, sold 1, outcome is 38
2022.jun 17 --> 37 --> Started at 38, sold 1, outcome is 37
2022.jun 24 --> 36 --> Started at 37, sold 1, outcome is 36
2022.jun 25 --> 35 --> Started at 36, sold 1, outcome is 35
and so on...
Filtering: 2022.06.10 - 2022.06.24
2022.jun 10 --> 38 --> Before 06.10 the stock is 39, sold 1, outcome is 38
2022.jun 17 --> 37 --> Started at 38, sold 1, outcome is 37
2022.jun 24 --> 36 --> Started at 37, sold 1, outcome is 36
If i close up the matrix i should see Stock quantity 36 as this is the last value of the filtering.
Relationships:
DocumentHeaderId DocumentNumber StoreId WarehouseId DocumentDate FulfillmentDate NetValue GrossValue MovementId Direction DirectionStr MovementTypeStr PartnerId PartnerCode PartnerName PartnerShortName 2 BSZ22-PC-0001 2 3 28.máj.22 10.máj.22 518624 611976,3 13 1 (+) Delivery 1 1 Paul Heumann SA Paul Heumann SA 9 RKA22-100-0001 2 3 02.jún.22 02.jún.22 274828 324297 18 -1 (-) Transfer - - - 10 RKV22-100-0001 2 2 02.jún.22 02.jún.22 274828 324297 19 1 (+) Transfer - - - 117 PNY22-100-0053 2 2 04.aug.22 04.aug.22 23498,88 29540 7 -1 (-) Cash Register - - - 111 PNY22-100-0051 2 2 02.aug.22 02.aug.22 10291,63 12665 7 -1 (-) Cash Register - - - 91 PNY22-100-0035 2 2 13.júl.22 13.júl.22 12993,27 15916 7 -1 (-) Cash Register - - - 79 PNY22-100-0030 2 2 07.júl.22 07.júl.22 10206 12430 7 -1 (-) Cash Register - - - 78 PNY22-100-0029 2 2 06.júl.22 06.júl.22 10573,46 12977 7 -1 (-) Cash Register - - - 68 PNY22-100-0023 2 2 29.jún.22 29.jún.22 10678,54 13298 7 -1 (-) Cash Register - - - 64 PNY22-100-0020 2 2 25.jún.22 25.jún.22 2525,42 2980 7 -1 (-) Cash Register - - - 44 PNY22-100-0013 2 2 17.jún.22 17.jún.22 14208,73 17470 7 -1 (-) Cash Register - - - 30 PNY22-100-0007 2 2 10.jún.22 10.jún.22 22485,84 28098 7 -1 (-) Cash Register - - - 27 PNY22-100-0006 2 2 09.jún.22 09.jún.22 23150,33 28408 7 -1 (-) Cash Register - - - DocumentHeaderId DocumentNumber Direction DirectionStr PartnerId StoreId WarehouseId ProductId ProductCode ProductName ProductTypeName ItemQuantityDir ItemNetPrice ItemGrossPrice ItemNetValuePromo ItemGrossValuePromo 117 PNY22-100-0053 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -2 1686,44 1990 3372,88 3980 10 RKV22-100-0001 1 (+) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal 20 1121 1322,78 22420 26455,6 9 RKA22-100-0001 -1 (-) 2 3 1 1 Heumann macesz dara (kóser) 400g Normal -20 1121 1322,78 22420 26455,6 2 BSZ22-PC-0001 1 (+) 1 2 3 1 1 Heumann macesz dara (kóser) 400g Normal 40 1121 1322,78 44840 52911,2 111 PNY22-100-0051 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 91 PNY22-100-0035 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 79 PNY22-100-0030 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 78 PNY22-100-0029 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 68 PNY22-100-0023 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 64 PNY22-100-0020 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 61 PNY22-100-0019 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 44 PNY22-100-0013 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 30 PNY22-100-0007 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 27 PNY22-100-0006 -1 (-) 2 2 1 1 Heumann macesz dara (kóser) 400g Normal -1 1686,44 1990 1686,44 1990 which of these samples is the header view and which is the item view? Also - what is your locale?
