Inventory Forecast Isuue

Hi Anonymous ,

The Table data is shown below:

Please follow these steps:

1.Add index column after grouping

Table.AddIndexColumn([Column],"Index",1)

2.Use the following DAX expression to create a measure

Balance =
VAR _Product_Code =
    SELECTEDVALUE ( 'Table'[Product Code] )
VAR _Month =
    SELECTEDVALUE ( 'Table'[Month] )
VAR _table =
    SUMMARIZE (
        ALL ( 'Table' ),
        [Product Code],
        [Month],
        "Index", MAX ( 'Table'[Index] ),
        "Column",
            SUM ( 'Table'[Inventory] ) + SUM ( 'Table'[PO] )
                + SUM ( 'Table'[SC] )
                - SUM ( 'Table'[SO] )
                - SUM ( 'Table'[BOM] )
    )
VAR _table2 =
    ADDCOLUMNS (
        _table,
        "Column2",
            MAXX (
                FILTER (
                    _table,
                    [Product Code] = EARLIER ( [Product Code] )
                        && [Index]
                            = EARLIER ( [Index] ) - 1
                ),
                [Column]
            )
    )
RETURN
    MAXX (
        FILTER ( _table2, [Product Code] = _Product_Code && [Month] = _Month ),
        [Column] + [Column2]
    )

3.Final output

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