Prorate values considering dates order.

[Anonymous]

Hello everybody,

I need your help, I need to prorate values considering dates.

1. I have a production table (quantities to be produced) by items on different dates.
2. I have a table per item and excess production that I would like to distribute in the items of my production table so as not to overproduce. but I must consider that the first dates are those that are covered with excesses.

Attached images.
Beforehand thank you very much.

[v-henryk-mstf]

Hi @Anonymous ,

I did a test and refer to the following results.

M1 = IF(MAX('Table'[Initial Excess])>=MAX('Table'[Req. Qty]),0,MAX('Table'[Req. Qty])-MAX('Table'[Initial Excess]))

M2 = IF(MAX('Table'[Initial Excess])>=MAX('Table'[Req. Qty]),MAX('Table'[Initial Excess])-MAX('Table'[Req. Qty]),0)

If it is not what you need, can you provide detailed instructions and screenshots of the expected results, I will answer you as soon as possible.

Let me know the result immediately, looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

Thank you,

I am sorry, I do not have the initial excess in the first table, since it is calculated according to what is covering the required amount.

[Greg_Deckler]

@Anonymous Not exactly sure what you are asking for, the Excess column or the New Req Qty column. Or are you just looking for the Exceso Final table? Can you post data as text?

[Anonymous]

I am looking for the New required quantity. considering that if the excess covers the first dates, it would be 0, and if the excess does not cover or is less than the initial amount, only the excess of this initial amount would be discounted. I hope I have explained myself and thank you very much for your help.

item Date Req. Qty Initial Excess New Req. Excess
Item 1 Date 1  50 75 0 25
Item 1 Date 2 25 25 0 0
Item 1 Date 3 32 0 32 0
Item 2 Date 1 24 240 0 216
Item 2 Date 2 56 216 0 160
Item 2 Date 3 98 160 0 62
Item 3 Date 1 10 20 0 10
Item 3 Date 2 30 10 20 0

Excess
Item | QTY
---------------------
Item 1 | 75
Item 2 | 240
item 3 | 20

In this case, the excess of item 3 does not cover the req. qty, so the new quantity considers 20 units that could not be covered with the excess.