Deducting Qty from different location in sequence

[Omid_123]

Hi,

I am trying to find a code that would help me with the below logic. Any help is highly appreciated.

Inputs:

• Jobs with quantities .
• Each job is sequenced based on a date.
• There are 4 inventory locations and they are prioritized based on consumption.

Logic: Deducation from Location 1, if location1 reaches to 0 qty then deduct from location2 and if location2 reaches to 0 qty then location3....... until location4 and if location4 is empty then "Order" text once it reaches negative.

Output:

• Clear or not clear
• If "Order" then short by how much in total.

Table 1
Job	Date	Part Number	Qty Required	Clear?	Short in Total
1	01-Jul-22	ABC	4	Clear
2	02-Jul-22	ABC	2	ORDER	-1
3	03-Jul-22	DEF	5	Clear
4	04-Jul-22	GHI	1	Clear
5	05-Jul-22	DEF	3	ORDER	-2
6	06-Jul-22	ABC	4	ORDER	-5

Table 2
Part Number	Location 1	Location 2	Location 3	Location 4
ABC	3	1	1	0
DEF	5	0	1	0
GHI	0	0	0	1

Thanks

[Anonymous · Accepted solution]

Hi  @Omid_123 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated columns as below to get the [Short in Total]

PS: If you have more locations, suggest that unpivot these location columns in Power Query Editor first...

Short in Total = 
VAR _tab2qty =
    CALCULATE (
        SUM ( 'Table 2 (2)'[Value] ),
        FILTER ( 'Table 2 (2)', 'Table 2 (2)'[Part Number] = 'Table 1'[Part Number] )
    )
    // CALCULATE (
    //     SUM ( 'Table 2'[Location 1] ),
    //     FILTER ( 'Table 2', 'Table 1'[Part Number] = 'Table 2'[Part Number] )
    // )
    //     + CALCULATE (
    //         SUM ( 'Table 2'[Location 2] ),
    //         FILTER ( 'Table 2', 'Table 1'[Part Number] = 'Table 2'[Part Number] )
    //     )
    //     + CALCULATE (
    //         SUM ( 'Table 2'[Location 3] ),
    //         FILTER ( 'Table 2', 'Table 1'[Part Number] = 'Table 2'[Part Number] )
    //     )
    //     + CALCULATE (
    //         SUM ( 'Table 2'[Location 4] ),
    //         FILTER ( 'Table 2', 'Table 1'[Part Number] = 'Table 2'[Part Number] )
    //     )
VAR _culqty =
    CALCULATE (
        SUM ( 'Table 1'[Qty Required] ),
        FILTER (
            'Table 1',
            'Table 1'[Part Number] = EARLIER ( 'Table 1'[Part Number] )
                && 'Table 1'[Date] <= EARLIER ( 'Table 1'[Date] )
        )
    )
RETURN
    IF ( _tab2qty - _culqty >= 0, BLANK (), _tab2qty - _culqty )

2. Create a calculated column as below to get the [Clear?]

Clear? = IF(ISBLANK([Short in Total]),"Clear","ORDER")

If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards