cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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)

 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

2 REPLIES 2
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?

Best regards,

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

Helper I

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors