Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tan_LC
Helper I
Helper I

DAX Formula to Calculate Stock to Order

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)

 

DateMaterial NoDaily Balance (kg)Usage (kg)Normal (Days)Stock Day(s)Re-order DateQty per Order/ContainerStock to Order (Desired column)Calculation (for reference)
30/10/2022A01353,910.50295.1435182.7       10,000 
31/10/2022A01346,799.85200.1535233.8       10,000 
04/11/2022A01346,000.00-  35        10,000 
05/11/2022A01346,000.00-  35        10,000 
06/11/2022A01344,925.28-  35        10,000 
18/11/2022A01333,000.00-  35        10,000 
19/11/2022A01331,686.14-  35        10,000 
30/11/2022A01340,473.84526.163576.9       10,000 
01/11/2022A01345,957.651,042.353544.101/11/2022      10,000 
02/11/2022A01345,813.421,186.583538.601/11/2022      10,000 
03/11/2022A01344,913.321,086.683541.301/11/2022      10,000 
07/11/2022A01342,925.281,074.723539.901/11/2022      10,000 
08/11/2022A01340,815.811,184.193534.501/11/2022      10,00010,00042.9
09/11/2022A01340,818.771,181.233534.601/11/2022      10,00010,00043
10/11/2022A01338,842.951,157.053533.601/11/2022      10,00010,00042.2
11/11/2022A01338,830.261,169.743533.201/11/2022      10,00010,00041.7
12/11/2022A01337,674.151,157.503532.501/11/2022      10,00010,00041.2
13/11/2022A01336,519.061,168.353531.301/11/2022      10,00010,00039.8
14/11/2022A01333,844.911,155.093529.301/11/2022      10,00010,00038
15/11/2022A01333,897.891,102.113530.801/11/2022      10,00010,00039.8
16/11/2022A01333,874.481,125.523530.101/11/2022      10,00010,00039
17/11/2022A01331,871.491,128.513528.201/11/2022      10,00010,00037.1
20/11/2022A01330,701.601,313.863523.401/11/2022      10,00020,00031
21/11/2022A01328,015.46984.543528.501/11/2022      10,00010,00038.6
22/11/2022A01347,927.261,072.743544.701/11/2022      10,000 
23/11/2022A01347,934.161,065.84354501/11/2022      10,000 
24/11/2022A01347,960.341,039.663546.101/11/2022      10,000 
25/11/2022A01345,979.361,020.64354501/11/2022      10,000 
26/11/2022A01344,810.95875.193551.201/11/2022      10,000 
27/11/2022A01343,975.511,313.863533.501/11/2022      10,00010,00041.1
28/11/2022A01341,164.56835.443549.301/11/2022      10,000 
29/11/2022A01341,184.37815.633550.501/11/2022      10,000 
01/12/2022A01340,023.93976.07354101/11/2022      10,000 
02/12/2022A01338,882.471,117.533534.801/11/2022      10,00010,00043.7
03/12/2022A01337,714.781,122.303533.601/11/2022      10,00010,00042.5
04/12/2022A01336,548.111,162.923531.401/11/2022      10,00010,00040
05/12/2022A01332,833.331,166.673528.101/11/2022      10,00010,00036.7
06/12/2022A01331,642.821,170.20352701/11/2022      10,00010,00035.6
07/12/2022A01330,425.371,186.983525.601/11/2022      10,00020,00034.1
08/12/2022A01329,226.571,217.45352401/11/2022      10,00020,00032.2
09/12/2022A01328,044.181,198.803523.401/11/2022      10,00020,00031.7
10/12/2022A01326,870.531,182.393522.701/11/2022      10,00020,00031.2
11/12/2022A01325,668.081,173.653521.901/11/2022      10,00020,00030.4
12/12/2022A01324,444.451,202.453520.301/11/2022      10,00020,00028.6
13/12/2022A01323,211.211,223.63351901/11/2022      10,00020,00027.1
14/12/2022A01321,992.021,233.243517.801/11/2022      10,00030,00025.9
15/12/2022A01320,815.071,219.193517.101/11/2022      10,00030,00025.3
30/10/2022B080 127,044.251,323.262396       22,000 
31/10/2022B080 125,501.40898.623139.7       22,000 
04/11/2022B080 117,400.00-  23        22,000 
05/11/2022B080 117,400.00-  23        22,000 
06/11/2022B080 112,628.50-  23        22,000 
18/11/2022B080 125,750.00-  23        22,000 
19/11/2022B080 119,798.86-  23        22,000 
30/11/2022B080 153,425.372,374.632364.6       22,000 
01/11/2022B080 118,814.674,585.332325.901/11/2022      22,000 
02/11/2022B080 118,147.075,252.932322.501/11/2022      22,00022,00026.7
03/11/2022B080 112,593.024,806.982323.401/11/2022      22,000 
07/11/2022B080 120,228.504,771.502325.201/11/2022      22,000 
08/11/2022B080 119,692.255,307.752322.601/11/2022      22,00022,00026.7
09/11/2022B080 137,873.125,326.882325.901/11/2022      22,000 
10/11/2022B080 131,373.325,226.682325.101/11/2022      22,000 
11/11/2022B080 127,079.415,320.592323.901/11/2022      22,000 
12/11/2022B080 121,757.415,289.51232301/11/2022      22,00022,00027.2
13/11/2022B080 116,454.495,353.082321.801/11/2022      22,00022,00025.9
14/11/2022B080 119,697.085,302.922322.601/11/2022      22,00022,00026.7
15/11/2022B080 111,522.755,077.25232201/11/2022      22,00022,00026.3
16/11/2022B080 107,759.105,190.902320.801/11/2022      22,00022,00025
17/11/2022B080 120,555.975,194.032323.201/11/2022      22,000 
20/11/2022B080 115,248.285,951.142319.401/11/2022      22,00022,00023.1
21/11/2022B080 175,799.424,550.582338.601/11/2022      22,000 
22/11/2022B080 162,851.494,948.512332.901/11/2022      22,000 
23/11/2022B080 158,114.134,885.872332.401/11/2022      22,000 
24/11/2022B080 158,269.964,730.042333.501/11/2022      22,000 
25/11/2022B080 177,948.764,651.242338.301/11/2022      22,000 
26/11/2022B080 172,636.434,012.43234301/11/2022      22,000 
27/11/2022B080 168,816.925,951.142328.401/11/2022      22,000 
28/11/2022B080 172,780.493,819.512345.201/11/2022      22,000 
29/11/2022B080 161,671.313,728.692343.401/11/2022      22,000 
01/12/2022B080 146,131.744,468.262332.701/11/2022      22,000 
02/12/2022B080 145,492.445,107.562328.501/11/2022      22,000 
03/12/2022B080 140,084.105,167.372327.101/11/2022      22,000 
04/12/2022B080 134,730.365,348.532325.201/11/2022      22,000 
05/12/2022B080 142,246.265,353.742326.601/11/2022      22,000 
06/12/2022B080 136,865.985,351.682325.601/11/2022      22,000 
07/12/2022B080 131,346.885,382.342324.401/11/2022      22,000 
08/12/2022B080 125,931.115,519.102322.801/11/2022      22,00022,00026.8
09/12/2022B080 120,591.235,415.772322.301/11/2022      22,00022,00026.3
10/12/2022B080 115,295.265,339.882321.601/11/2022      22,00022,00025.7
11/12/2022B080 109,908.335,295.972320.801/11/2022      22,00022,00024.9
12/12/2022B080 104,428.405,386.932319.401/11/2022      22,00022,00023.5
13/12/2022B08098,889.415,479.93231801/11/2022      22,00044,00022.1
14/12/2022B08093,392.425,538.992316.901/11/2022      22,00044,00020.8
15/12/2022B08088,030.885,496.99231601/11/2022      22,00044,00020

 

Thanks & regards,

Lee Cheng

2 REPLIES 2
v-yadongf-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors