When can i deliver?

[Oomsen]

My sales collegeas would like to know when we can deliver a potential order.

Let's say we got an order of 8 x Robot A.

To build Robot A we have a multilevel BOM. 

In the BOM is set which quantity of Part 1, Part 2 and Part 3 is necessary to make 1 x Robot A.

In the BOM is also set which quantity of Part ABC is necessary to make 1 x Part 1.

Every Part has his own lead time. 

I would like to build a report in which i can set Robot A x 8 and then get a view of maximum lead time and necessary quantity. 

Currently i have 2 tables. Items [Item code] and BOM [partitemcode].

It looks like below:

Item code     Partitemcode      Quantity

Part 1            Part ABC             3

                     Part DEF              1

                      Part GHI              2

Part 2             Part JKL              4

[v-juanli-msft]

Hi @Oomsen 

Assume you have a table as below

"components one robot" table

item code	partitemcode	quantity of part	quantity of item	time/part/quantity
part1	part abc	3	1	1
part1	part def	1	1	2
part1	part ghi	2	1	3
part2	part jkl	4	2	1
part3	part nmp	2	1	2

I create columns in this table

time per part = [quantity of part]*[time/part/quantity]

time per item = CALCULATE(SUM('components one robot'[time per part]),ALLEXCEPT('components one robot','components one robot'[item code]))

Then i create another table

time per item =
CALCULATE (
    SUM ( 'components one robot'[time per part] ),
    ALLEXCEPT ( 'components one robot', 'components one robot'[item code] )
)

Add columns in this table

lead time one robot = [quantity of item]*[time per item]

add measures

lead time of 8 robot = SUM('lead time one robot'[lead time one robot])*8

quantity of items for 8 robots = SUM('lead time one robot'[quantity of item])*8

total quantity of parts for 8 robots = SUM('lead time one robot'[total quantity of parts])*8

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Oomsen 

Assume you have tables as below

Item table

item code

part1

part2

part3

Bom table

item code	partitemcode	quantity	robot	quantity of item	time/part/quantity
part1	part abc	3	robot a	1	1
part1	part def	1	robot a	1	2
part1	part ghi	2	robot a	1	3
part2	part jkl	4	robot a	2	1
part3	part nmp	2	robot a	2	2
part1	part abc	2	robot b	1	1
part1	part def	3	robot b	1	2
part2	part jkl	1	robot b	2	1
part3	part nmp	2	robot b	3	2

If my assumption is not correct, please show me an simple example dataset.

Best Regards
Maggie

[Oomsen]

@v-juanli-msft 

The column robot isn't set in my bom table. This is because there are common parts between the robots.