Order creation based on quantity produced in addition

[wilijam]

Dears.

I would like to get real quantity to produce based on criteries and if produced quantity is greater than needed the next requestet quantity will be reduced by this quantity.

I will try to explain with an example below.

My table is "SD_FORECAST"

I have to round order quantity by SCRAP and rounding value

    - SCRAP value is 10%

    - Rounding value is 36 pcs

I used DAX:

To produce with scrap, with rounding =

VAR X1 =

ROUNDUP (

IFERROR (

IF (

SD_FORECAST[Order quantity] < 0,

( SD_FORECAST[Order quantity] * ( -1 ) )

/ (

1

- ( 0,10)

)

/ 36),

0

),

1

),

0

)

* 36)

To produce with scrap, with rounding - Total quantity I need to produce

Totoal produced OK parts with rounding - All OK parts form production quantity with rounding

OVER PRODUCTION - total OK quantity produced over the order (Total produced OK parts with rounding + order quantity)

What I want now is to calculate with OVER PRODUCTION and create column "Production orders" where :

First row OVER PRODUCTION is 22 it means that i cover orders for 02.12., 03.12. and partly for 04.12.

For 04.12. remains 58 so there should be created another request for production based on conditions mentioned abowe.

I appreciate all your advice.

Regards

Viliam

[v-angzheng-msft]

Hi, @wilijam 

What is the formula for the Total produced OK parts with rounding column? What is the calculation logic for the Production orders column? Are there other columns involved in the calculation?

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

Best Regards,
Community Support Team _ Zeon Zheng

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

[lbendlin]

You cannot do that in DAX, PRODUCTX() does not allow for in-step manipulation (like rounding). You can use Power Query List.Accumulate() which allows such operations during each iteration.

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.