Cumulative inventory Forecast

Hi Anonymous 

I update my tables and my measure.

Forecast Table:

On Hand Table:

Safety Stock Table:

Measures:

Stock =
VAR _CHANGE =
    SUMX (
        FILTER (
            ALL ( Forecast ),
            Forecast[Planned Dates] <= MAX ( Forecast[Planned Dates] )
                && Forecast[Material] = MAX ( Forecast[Material] )
                && Forecast[Storage Location] = MAX ( Forecast[Storage Location] )
        ),
        Forecast[Quantity]
    )
VAR _Total =
    SUMX (
        FILTER (
            'On Hand',
            'On Hand'[Material] = MAX ( Forecast[Material] )
                && 'On Hand'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
        ),
        'On Hand'[Total Stock]
    )
RETURN
    _CHANGE + _Total

Color =
IF (
    [Stock]
        >= CALCULATE (
            MAX ( 'Safety stock'[SafeQT] ),
            FILTER (
                'Safety stock',
                'Safety stock'[Material] = MAX ( Forecast[Material] )
                    && 'Safety stock'[Storage Location] = MAX ( 'Forecast'[Storage Location] )
            )
        ),
    1,
    0
)

Result:

If this reply still couldn't help you solve your problem please show me more details about your tables.

Here I have some question:

1. In your On Hand Table: Storage Location for BA000408001 is BA01. However in your Forecast Table, Storage Location is Blank when reqd quantity <0, and Storage Location is IT01 when reqd quantity>0. I couldn't find BA01 in your Forecast Table.

2. Does each material have the same planned dates like my sample?

You can download the pbix file from this link: Cumulative inventory Forecast

Best Regards,

Rico Zhou

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