Forecasting beginning month inventory based on usage forecast

[chad_clift]

I'm trying to create a measure that rolls the beginning of month inventory forecast based on the prior month's forecast.  The next month forecast is previous month stock (either actual or forecast) minus the usage in that month to get to a new forecast for the following month.  Below is where I'm currently at.  Everything seems to work until I get to September.  I should see 9/1/2022 have -537,045 (August BOM Forecast - August Usage Actual/Forecast), instead it's taking the prior 2 months usage.  BOM Forecast is the actuals as of the current month (6/1/2022).  Future months are all forecasts.

[v-yanjiang-msft · Accepted solution]

Hi @chad_clift ,

According to your description, it's related to your measure formula. I create a sample and here's my solution.

Create a measure.

BOM Forecast =
VAR _Lastnonblank =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[BOM on hand] <> BLANK () ),
        'Table'[Month]
    )
RETURN
    IF (
        MAX ( 'Table'[BOM on hand] ) <> BLANK (),
        MAX ( 'Table'[BOM on hand] ),
        MAXX (
            FILTER ( ALL ( 'Table' ), 'Table'[Month] = _Lastnonblank ),
            'Table'[BOM on hand]
        )
            - SUMX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Month] >= _Lastnonblank
                        && 'Table'[Month] <= MAX ( 'Table'[Month] )
                ),
                'Table'[Usage Actual/Forecast]
            )
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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