predicted stock calculations based on conditions

[ValeriaBreve]

Hello,

I need expert help to understand how to tackle this scenario.

We have finished products that can be made with alternative raw materials. I am calculating the stock of the raw materials, beginning with a start inventory and depleting every consumption I get from the system.

However, stock might go negative at a certain moment. In this case, the stock needs to conumed to 0, and the residual consumption needs to be inputted to the alternative raw material, which will also have a starting inventory and other consumptions of its own.

In the stock level, deliveries must also be taken into account, so if a raw material goes negative, it might be replenished at a later date and therefore stock can be drawn again from there.

What's the best way to achieve this?

Date	Raw Material	Alternative Raw Mat	Requirement	Stock Level	Type	Finished Product
6/26/2025	Raw Mat 1	Raw Mat 2		200	Starting Inventory
6/27/2025	Raw Mat 1	Raw Mat 2	150	50	Consumption	Prod A
6/28/2025	Raw Mat 1	Raw Mat 2	150	-100	Consumption	Prod A
6/26/2025	Raw Mat 2			3000	Starting Inventory
6/27/2025	Raw Mat 2		1000	2000	Consumption	Prod B
6/28/2025	Raw Mat 2	Raw Mat 1	100	1900	Consumption	Prod A
7/1/2025	Raw Mat 1			2000	Delivery

I hope I was clear enough with the explanations - thank you!

[v-sdhruv]

Hi @ValeriaBreve  ,
Just wanted to check if you had the opportunity to review the solutions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @ValeriaBreve ,

Using Power query in this case seems like a good approach since recursion in DAX is quite tricky but not impossible.
You can achieve this via DAX by creating a 2 calculated columns. One for calculating primary stock and another for calculating Alt material consumption.

Create a calculated column-

PrimaryMaterialStock = 
VAR CurrenttDate = 'InventoryTransactions'[Date]
VAR Material = 'InventoryTransactions'[Raw Material]

VAR RunningAdditions =
    CALCULATE(
        SUM('InventoryTransactions'[Stock Level]),
        FILTER(
            'InventoryTransactions',
            'InventoryTransactions'[Raw Material] = Material &&
            'InventoryTransactions'[Type] IN {"Starting Inventory", "Delivery"} &&
            'InventoryTransactions'[Date] <= CurrenttDate
        )
    )

VAR RunningConsumptions =
    CALCULATE(
        SUM('InventoryTransactions'[Requirement]),
        FILTER(
            'InventoryTransactions',
            'InventoryTransactions'[Raw Material] = Material &&
            'InventoryTransactions'[Type] = "Consumption" &&
            'InventoryTransactions'[Date] <= CurrenttDate
        )
    )

RETURN
    RunningAdditions - RunningConsumptions

Create another calculated column for Alt material consumption.

AltMaterialConsumption = 
VAR Req = 'InventoryTransactions'[Requirement]
VAR PrimaryStock = 'InventoryTransactions'[PrimaryMaterialStock]
VAR IsConsumption = 'InventoryTransactions'[Type] = "Consumption"

RETURN
    IF(
        IsConsumption && NOT ISBLANK(Req),
        MAX(0, Req - MAX(0, PrimaryStock)),
        BLANK()
    )

This will give the following result-

Attached file for reference.

Hope this helps!
If the response has addressed your query, please accept it as a solution so that other members can easily find it.
Thank you.

[FBergamaschi]

this calculation is very complex and becomes recursive

The problem is also that there should be a clear sequence to follow, while I do not see how to understand a sequence in this rows when the date is the same

We can try helping you bt this kind of calculations shoud be done in SQL (so at the source level)

If you can add a column of sequence we can try but are you looking for a column or a measure?

Please try to get the solution by your IT in SQL if we cannot make it (I need to think about it a bit)

[ValeriaBreve]

@FBergamaschi thank you! Yes in my table the time got squeezed out - sorry - here's the corrected table with date and time:

Date	Raw Material	Alternative Raw Mat	Requirement	Stock Level	Type	Finished Product
6/26/25 8:00 AM	Raw Mat 1	Raw Mat 2		200	Starting Inventory
6/27/25 2:00 AM	Raw Mat 1	Raw Mat 2	150	50	Consumption	Prod A
6/28/25 3:00 PM	Raw Mat 1	Raw Mat 2	150	-100	Consumption	Prod A
6/26/25 10:00 AM	Raw Mat 2			3000	Starting Inventory
6/27/25 6:00 AM	Raw Mat 2		1000	2000	Consumption	Prod B
6/28/25 12:00 PM	Raw Mat 2	Raw Mat 1	100	1900	Consumption	Prod A
7/1/25 6:00 PM	Raw Mat 1			2000	Delivery

So there is  sequence based on date/time.

I am using the final calculation exposed in a table that has the same granularity as the data - so either a calculated column or meausre could do the tick.

I have come to a simplified solution idea where I am looking at where the initial stock is negative for a material, then I am taking in the replacement raw material with the calculated quantity (residual stock of the first raw material minus Requirement). I am doing this in Powerquery; I am then adding these "replacement" lines that I have treated as a separate table into the main table, and then I am re-calculating stock levels from there. 

It's a simplification to start with something...

But I am very interested in other better ideas... thanks!