Forum Discussion

chad_clift's avatar
chad_clift
Helper I
4 years ago
Solved

Forecasting beginning month inventory based on usage forecast

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.

 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.