DAX Formula to Calculate Stock to Order

[Tan_LC]

Hi, in below table, I would like to calculate Stock to Order (Desired column).

1. Stock to Order should be calculated when Re-order Date is not blank

2. Stock to Order should be multiplication of Qty per Order/Container. Eg: 10,000, 20,000 & etc... according to its Material No

3. Calculation Method: [Daily Balance (kg) + Stock to Order (Desired column)] / Usage (kg) ≥ Normal (Days)

Date	Material No	Daily Balance (kg)	Usage (kg)	Normal (Days)	Stock Day(s)	Re-order Date	Qty per Order/Container	Stock to Order (Desired column)	Calculation (for reference)
30/10/2022	A013	53,910.50	295.14	35	182.7		10,000
31/10/2022	A013	46,799.85	200.15	35	233.8		10,000
04/11/2022	A013	46,000.00	-	35			10,000
05/11/2022	A013	46,000.00	-	35			10,000
06/11/2022	A013	44,925.28	-	35			10,000
18/11/2022	A013	33,000.00	-	35			10,000
19/11/2022	A013	31,686.14	-	35			10,000
30/11/2022	A013	40,473.84	526.16	35	76.9		10,000
01/11/2022	A013	45,957.65	1,042.35	35	44.1	01/11/2022	10,000
02/11/2022	A013	45,813.42	1,186.58	35	38.6	01/11/2022	10,000
03/11/2022	A013	44,913.32	1,086.68	35	41.3	01/11/2022	10,000
07/11/2022	A013	42,925.28	1,074.72	35	39.9	01/11/2022	10,000
08/11/2022	A013	40,815.81	1,184.19	35	34.5	01/11/2022	10,000	10,000	42.9
09/11/2022	A013	40,818.77	1,181.23	35	34.6	01/11/2022	10,000	10,000	43
10/11/2022	A013	38,842.95	1,157.05	35	33.6	01/11/2022	10,000	10,000	42.2
11/11/2022	A013	38,830.26	1,169.74	35	33.2	01/11/2022	10,000	10,000	41.7
12/11/2022	A013	37,674.15	1,157.50	35	32.5	01/11/2022	10,000	10,000	41.2
13/11/2022	A013	36,519.06	1,168.35	35	31.3	01/11/2022	10,000	10,000	39.8
14/11/2022	A013	33,844.91	1,155.09	35	29.3	01/11/2022	10,000	10,000	38
15/11/2022	A013	33,897.89	1,102.11	35	30.8	01/11/2022	10,000	10,000	39.8
16/11/2022	A013	33,874.48	1,125.52	35	30.1	01/11/2022	10,000	10,000	39
17/11/2022	A013	31,871.49	1,128.51	35	28.2	01/11/2022	10,000	10,000	37.1
20/11/2022	A013	30,701.60	1,313.86	35	23.4	01/11/2022	10,000	20,000	31
21/11/2022	A013	28,015.46	984.54	35	28.5	01/11/2022	10,000	10,000	38.6
22/11/2022	A013	47,927.26	1,072.74	35	44.7	01/11/2022	10,000
23/11/2022	A013	47,934.16	1,065.84	35	45	01/11/2022	10,000
24/11/2022	A013	47,960.34	1,039.66	35	46.1	01/11/2022	10,000
25/11/2022	A013	45,979.36	1,020.64	35	45	01/11/2022	10,000
26/11/2022	A013	44,810.95	875.19	35	51.2	01/11/2022	10,000
27/11/2022	A013	43,975.51	1,313.86	35	33.5	01/11/2022	10,000	10,000	41.1
28/11/2022	A013	41,164.56	835.44	35	49.3	01/11/2022	10,000
29/11/2022	A013	41,184.37	815.63	35	50.5	01/11/2022	10,000
01/12/2022	A013	40,023.93	976.07	35	41	01/11/2022	10,000
02/12/2022	A013	38,882.47	1,117.53	35	34.8	01/11/2022	10,000	10,000	43.7
03/12/2022	A013	37,714.78	1,122.30	35	33.6	01/11/2022	10,000	10,000	42.5
04/12/2022	A013	36,548.11	1,162.92	35	31.4	01/11/2022	10,000	10,000	40
05/12/2022	A013	32,833.33	1,166.67	35	28.1	01/11/2022	10,000	10,000	36.7
06/12/2022	A013	31,642.82	1,170.20	35	27	01/11/2022	10,000	10,000	35.6
07/12/2022	A013	30,425.37	1,186.98	35	25.6	01/11/2022	10,000	20,000	34.1
08/12/2022	A013	29,226.57	1,217.45	35	24	01/11/2022	10,000	20,000	32.2
09/12/2022	A013	28,044.18	1,198.80	35	23.4	01/11/2022	10,000	20,000	31.7
10/12/2022	A013	26,870.53	1,182.39	35	22.7	01/11/2022	10,000	20,000	31.2
11/12/2022	A013	25,668.08	1,173.65	35	21.9	01/11/2022	10,000	20,000	30.4
12/12/2022	A013	24,444.45	1,202.45	35	20.3	01/11/2022	10,000	20,000	28.6
13/12/2022	A013	23,211.21	1,223.63	35	19	01/11/2022	10,000	20,000	27.1
14/12/2022	A013	21,992.02	1,233.24	35	17.8	01/11/2022	10,000	30,000	25.9
15/12/2022	A013	20,815.07	1,219.19	35	17.1	01/11/2022	10,000	30,000	25.3
30/10/2022	B080	127,044.25	1,323.26	23	96		22,000
31/10/2022	B080	125,501.40	898.6	23	139.7		22,000
04/11/2022	B080	117,400.00	-	23			22,000
05/11/2022	B080	117,400.00	-	23			22,000
06/11/2022	B080	112,628.50	-	23			22,000
18/11/2022	B080	125,750.00	-	23			22,000
19/11/2022	B080	119,798.86	-	23			22,000
30/11/2022	B080	153,425.37	2,374.63	23	64.6		22,000
01/11/2022	B080	118,814.67	4,585.33	23	25.9	01/11/2022	22,000
02/11/2022	B080	118,147.07	5,252.93	23	22.5	01/11/2022	22,000	22,000	26.7
03/11/2022	B080	112,593.02	4,806.98	23	23.4	01/11/2022	22,000
07/11/2022	B080	120,228.50	4,771.50	23	25.2	01/11/2022	22,000
08/11/2022	B080	119,692.25	5,307.75	23	22.6	01/11/2022	22,000	22,000	26.7
09/11/2022	B080	137,873.12	5,326.88	23	25.9	01/11/2022	22,000
10/11/2022	B080	131,373.32	5,226.68	23	25.1	01/11/2022	22,000
11/11/2022	B080	127,079.41	5,320.59	23	23.9	01/11/2022	22,000
12/11/2022	B080	121,757.41	5,289.51	23	23	01/11/2022	22,000	22,000	27.2
13/11/2022	B080	116,454.49	5,353.08	23	21.8	01/11/2022	22,000	22,000	25.9
14/11/2022	B080	119,697.08	5,302.92	23	22.6	01/11/2022	22,000	22,000	26.7
15/11/2022	B080	111,522.75	5,077.25	23	22	01/11/2022	22,000	22,000	26.3
16/11/2022	B080	107,759.10	5,190.90	23	20.8	01/11/2022	22,000	22,000	25
17/11/2022	B080	120,555.97	5,194.03	23	23.2	01/11/2022	22,000
20/11/2022	B080	115,248.28	5,951.14	23	19.4	01/11/2022	22,000	22,000	23.1
21/11/2022	B080	175,799.42	4,550.58	23	38.6	01/11/2022	22,000
22/11/2022	B080	162,851.49	4,948.51	23	32.9	01/11/2022	22,000
23/11/2022	B080	158,114.13	4,885.87	23	32.4	01/11/2022	22,000
24/11/2022	B080	158,269.96	4,730.04	23	33.5	01/11/2022	22,000
25/11/2022	B080	177,948.76	4,651.24	23	38.3	01/11/2022	22,000
26/11/2022	B080	172,636.43	4,012.43	23	43	01/11/2022	22,000
27/11/2022	B080	168,816.92	5,951.14	23	28.4	01/11/2022	22,000
28/11/2022	B080	172,780.49	3,819.51	23	45.2	01/11/2022	22,000
29/11/2022	B080	161,671.31	3,728.69	23	43.4	01/11/2022	22,000
01/12/2022	B080	146,131.74	4,468.26	23	32.7	01/11/2022	22,000
02/12/2022	B080	145,492.44	5,107.56	23	28.5	01/11/2022	22,000
03/12/2022	B080	140,084.10	5,167.37	23	27.1	01/11/2022	22,000
04/12/2022	B080	134,730.36	5,348.53	23	25.2	01/11/2022	22,000
05/12/2022	B080	142,246.26	5,353.74	23	26.6	01/11/2022	22,000
06/12/2022	B080	136,865.98	5,351.68	23	25.6	01/11/2022	22,000
07/12/2022	B080	131,346.88	5,382.34	23	24.4	01/11/2022	22,000
08/12/2022	B080	125,931.11	5,519.10	23	22.8	01/11/2022	22,000	22,000	26.8
09/12/2022	B080	120,591.23	5,415.77	23	22.3	01/11/2022	22,000	22,000	26.3
10/12/2022	B080	115,295.26	5,339.88	23	21.6	01/11/2022	22,000	22,000	25.7
11/12/2022	B080	109,908.33	5,295.97	23	20.8	01/11/2022	22,000	22,000	24.9
12/12/2022	B080	104,428.40	5,386.93	23	19.4	01/11/2022	22,000	22,000	23.5
13/12/2022	B080	98,889.41	5,479.93	23	18	01/11/2022	22,000	44,000	22.1
14/12/2022	B080	93,392.42	5,538.99	23	16.9	01/11/2022	22,000	44,000	20.8
15/12/2022	B080	88,030.88	5,496.99	23	16	01/11/2022	22,000	44,000	20

Thanks & regards,

Lee Cheng

[v-yadongf-msft]

Hi @Tan_LC ,

In order to better understanding your demands and give the right solution, could you please tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tan_LC]

Hi Yadong,

In the table, there are Material No. A013 & B080 with the date range from Oct till Dec respectively.

I would like to calculate the "Stock to Order" for each material (as refer to "Stock to Order (Desired column)").

This "Stock to Order" should be triggered by 2 criterias which are Re-order Date must not be blank and Stock Day(s) is less than Normal (Days).

I need a formula to give me the result exactly same as the one shows in "Stock to Order (Desired column)".

In "Calculation (for reference)", it is actually the Stock Days after includes the Stock to Order quantity. This column is just for reference and it can be ignored.

For calculation method, please referred to the first post.

Thanks.

Regards,

Lee Cheng