DAX Forecasting help

Hi cottrera ,

It is certain that there is a problem with your filter conditions.

First, dax only calculate with the data which already existing, WIP calculates depend on itself which not existing.

But this don't mean it impossible to forecast the WIP. We can easily find that

the next WIP = current WIP + sum( current Variance)/2 

after the next WIP = current WIP + sum( current Variance + the next Variance )/2

second after the next WIP  = current WIP + sum( current Variance + the next Variance + after the next Variance )/2

...

So we should construct a cache table to calculate.

Try the following code to create a measure:

Forecast WIP M =
VAR _firstrow =
    TOPN( 1, FILTER( ALL( 'Table' ), NOT ( ISBLANK( [WIP] ) ) ), [Month], DESC )
VAR _lastMonth =
    CALCULATE( MAX( 'Table'[Month] ), _firstrow )
VAR _lastWIP =
    CALCULATE( MAX( 'Table'[WIP] ), _firstrow )
VAR _sumVar =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            [Month] >= _lastMonth
                && [Month] <= SELECTEDVALUE( 'Table'[Month] )
        ),
        [Variance]
    ) / 2
VAR _forecast = _lastWIP + _sumVar
RETURN
    IF( SUM( 'Table'[WIP] ) = BLANK(), _forecast, SUM( 'Table'[WIP] ) )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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