Stock quantity counting help

[Akos07]

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.

VAR varDate = SELECTEDVALUE(BI_DocumentItemsView[BI_DocumentHeadersView.FulfillmentDate_Local_Date])

VAR minDate =

Calculate(

MIN(BI_DocumentItemsView[BI_DocumentHeadersView.FulfillmentDate_Local_Date]),

All(BI_DocumentItemsView)

)

VAR varProduct = SELECTEDVALUE(BI_DocumentItemsView[ProductId])

RETURN

CALCULATE(

SUM(BI_DocumentItemsView[ItemQuantityDir]),

BI_DocumentItemsView[BI_DocumentHeadersView.FulfillmentDate_Local_Date] <= varDate

&& BI_DocumentItemsView[BI_DocumentHeadersView.FulfillmentDate_Local_Date] >= minDate

&& BI_DocumentItemsView[ProductId] = varProduct,

FILTER(Movements,Movements[Stock]= TRUE())

)

Also if i close the product the row total aint 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.

[Akos07]

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		-	-	-

[Akos07]

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

[lbendlin]

which of these samples is the header view and which is the item view?  Also - what is your locale?

[Akos07]

Message 4 of 6 Head, 

Message 5 of 6 Item

Eu - Hungary

[lbendlin]

There is a product with document header id 61 without an equivalent entry in the header table. What date is it for?

[Akos07]

Hi @lbendlin 

Could you give some guidance to this problem, how to solve it?

[Akos07]

sorry, its 2022.06.24

[lbendlin]

sorry, looks like the entire row for item 61 is needed in the header table. or can it be dropped from the details table?

[Akos07 · Accepted solution]

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.

[lbendlin]

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-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Akos07]

Hello,

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: