3 Calculated Columns - Values in different rows

[a__miller]

Hi, I'm struggling to know where to start with these 3 calculated columns. I attached an excel with 3 yellow columns that I'm trying to replicate in power bi, but I don't know what DAX I can use to create the correct calculated columns.

Is there a way to calculate the 3 column quantities for each category? I'm looking to see what order quantity can be filled based on the next inventory quantity coming in, what will be short, and the remaining inventory once the order is filled. Thank you in advance!

Here's a snapshot of excel.

here's a copy of the table

Category	Date	Incoming Inventory	Order	Filled Order quantity	Order Shortage Quantity	Remaining Inventory
A	3/1/2022
A	3/2/2022
A	3/3/2022		500		500
A	3/4/2022	1000		500	0	500
A	3/5/2022
A	3/6/2022
A	3/7/2022		1000	500	500	0
A	3/8/2022
A	3/9/2022	2000		500	0	1500
A	3/10/2022
A	3/11/2022		2000	1500	500	0
A	3/12/2022
A	3/13/2022
A	3/14/2022	1500		500	0	1000
A	3/15/2022		300	300	0	700
A	3/16/2022
B	3/1/2022
B	3/2/2022		625		625
B	3/3/2022	1250		625		625
B	3/4/2022
B	3/5/2022
B	3/6/2022		1250	625	625
B	3/7/2022
B	3/8/2022	2500		625		1875
B	3/9/2022
B	3/10/2022		2500	1875	625
B	3/11/2022
B	3/12/2022
B	3/13/2022	1875		625		1250
B	3/14/2022		375	375		875
B	3/15/2022
B	3/16/2022

[v-henryk-mstf · Accepted solution]

Hi @a__miller ,

You can try the following formula: if the result is positive, it means the remaining stock is sufficient; if the result is negative, it means the stock is insufficient and needs to be replenished.

M =
CALCULATE (
    SUM ( 'Table'[Remaining Inventory] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
)
    - CALCULATE (
        SUM ( 'Table'[Order Shortage Quantity] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
    )

If the problem is still not resolved, please provide detailed error information and desired results screenshot. 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.