Calculation of Order Qty based on Minimum Level

[TanLC]

Hi, in below table, I would like to calculate Order Status, Daily Balance (inc. Ordered Qty) (kg) and Stock Day(s) (with Ordered Qty).

Note: Stock Day(s) should always above Standard Safety Stock Day(s).

1. If the Stock Day(s) < Standard Safety Stock Day(s), then Standard Order Qty of 3,000 to be added into Daily Balance (kg) and divide by Usage (kg) to ensure Stock Day(s) (with Ordered Qty) is above Standard Safety Stock Day(s).

2. Above steps to be repeated whenever the Stock Day(s) is < Standard Safety Stock Day(s).

Kindly help.

Date	Usage (kg)	Daily Balance (kg)	Standard Safety Stock Day(s)	Stock Day(s)	Standard Order Qty	Order Status (Desired)	Daily Balance (inc. Ordered Qty) (kg) (Desired)	Stock Day(s) (with Ordered Qty) (Desired)
1/4/2025	0	7,377.83	40	42.6	3,000		-
2/4/2025	0	7,377.83	40	42.6	3,000		-
3/4/2025	0	7,176.13	40	42.6	3,000		-
4/4/2025	201.7	6,974.43	40	34.6	3,000	Yes	9,974.43	49.5
5/4/2025	201.7	6,772.73	40	33.6	3,000		9,772.73	48.5
6/4/2025	201.7	6,571.03	40	32.6	3,000		9,571.03	47.5
7/4/2025	201.7	6,369.34	40	31.6	3,000		9,369.34	46.5
8/4/2025	201.7	6,167.64	40	30.6	3,000		9,167.64	45.5
9/4/2025	201.7	5,965.94	40	29.6	3,000		8,965.94	44.5
10/4/2025	201.7	5,764.24	40	28.6	3,000		8,764.24	43.5
11/4/2025	201.7	5,562.55	40	27.6	3,000		8,562.55	42.5
12/4/2025	201.7	5,360.85	40	26.6	3,000		8,360.85	41.5
13/4/2025	201.7	5,159.15	40	25.6	3,000		8,159.15	40.5
14/4/2025	201.7	4,957.45	40	24.6	3,000	Yes	10,957.45	54.3
15/4/2025	201.7	4,755.76	40	23.6	3,000		10,755.75	53.3
16/4/2025	201.7	4,554.06	40	22.6	3,000		10,554.05	52.3
17/4/2025	201.7	4,352.36	40	21.6	3,000		10,352.35	51.3
18/4/2025	201.7	4,150.66	40	20.6	3,000		10,150.65	50.3
19/4/2025	201.7	3,948.96	40	19.6	3,000		9,948.95	49.3
20/4/2025	201.7	3,747.27	40	18.6	3,000		9,747.25	48.3
21/4/2025	201.7	3,545.57	40	17.6	3,000		9,545.55	47.3
22/4/2025	201.7	3,343.87	40	16.6	3,000		9,343.85	46.3
23/4/2025	201.7	3,142.17	40	15.6	3,000		9,142.15	45.3
24/4/2025	201.7	2,940.48	40	14.6	3,000		8,940.45	44.3
25/4/2025	201.7	2,738.78	40	13.6	3,000		8,738.75	43.3
26/4/2025	201.7	2,537.08	40	12.6	3,000		8,537.05	42.3
27/4/2025	201.7	2,335.38	40	11.6	3,000		8,335.35	41.3
28/4/2025	201.7	2,133.68	40	10.6	3,000		8,133.65	40.3
29/4/2025	201.7	1,931.99	40	9.6	3,000	Yes	10,931.95	54.2
30/4/2025	201.7	1,931.99	40	9.6	3,000		10,730.25	53.2

Thanks.

Regards,

TanLC

[Anonymous]

Hi ,

Based on the information, creating the index column.

Try using the following DAX formula to create new column to calculate the order status, daily balance, stock days.

Daily Balance desired = 
VAR CurrentBalance = [Daily Balance (kg)]
VAR Usage = [Usage (kg)]
VAR StandardOrderQty = 3000
VAR NewBalance = 
    IF(
        [Index] >= 14 && [Index] < 29, CurrentBalance + StandardOrderQty * 2,
        IF([Index] >= 29, CurrentBalance + StandardOrderQty * 3, IF([Index] < 4, BLANK(), CurrentBalance + StandardOrderQty))
    )
RETURN
    NewBalance

Stock Day desired = DIVIDE([Daily Balance desired], [Usage (kg)])

The result is shown below.

Best Regards,

Wisdom Wu

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

[TanLC]

Hi @Anonymous , thanks for the help.

Using 14 & 29 as a constant value in the DAX Calculation isn't appropriate as this figure will change when days go by.  The reason I put a "Yes" on 14/04/2025 & 29/04/2025 is due to Stock Days will be less than Standard Safety Stock Day(s) of 40 Days after the 1st top up of 3,000 qty. So this "Yes" indicator should be calculated using Measure instead of make it as a constant value.

Regards,

TanLC

[Anonymous]

Hi ,

If stock days is less than 40 and set to Yes, then it should be set to Yes for #4 and beyond, but only when the expected stock days is less than 40, depending on your requirements. The expected stock days is again calculated based on the expected daily balance, and that “yes” can't be judged based on the calculated stock days. It may not be calculated.

Best Regards,

Wisdom Wu

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

[Irwan]

hello @TanLC 

i might be missed the information but how do you define 'Order Status'?

On what condition will it become "Yes"?
Thank you.

[TanLC]

Hi @Irwan , Order Status will show "Yes" if Stock Day(s) < Standard Safety Stock Day(s). Thus, Standard Order Qty to be added onto Daily Balance (kg).

Thank you.

[Irwan]

hello @TanLC 

i see, so what condition when the addition becomes double/triple?

in 4/4/25, the value will add Standar Order Qty once (6974.43+3000=9974.43).

But in 14/4/25, it will add Standar Order Qty twice (4957.45+2x3000=10957.45).

then in 29/4/25, it will add Standar Order Qty three times (1931.99+3x3000=10931.95).

Thank you.

[TanLC]

Hi @Irwan,

On 14/04/2025, result of 10,957.45 was actually based on below calculation:

8,159.15 [Daily Balance (inc. Ordered Qty) (kg) on 13/04/2025]

- 201.7 [Usage (kg)on 14/04/2025]

+ 3,000 [Standard Order Qty on 14/04/2025]

Same goes to 29/04/2025.