Creating a Aggregated Inventory measure

Hi, Dor-Y13 

Based on the data provided in your image and the expected results of the yellow column metrics, I used the following SAMPLE example data:

I created a metric using the following DAX expression:

Stock aggregated balance = 
VAR _table =
    CALCULATETABLE (
        SUMMARIZE (
            'Table2',
            'Table2'[Base date],
            'Table2'[Stock In],
            'Table2'[Stock out],
            "_rolling1",
                VAR _total =
                    CALCULATE (
                        SUM ( Table2[Stock In] ),
                        FILTER (
                            ALL ( 'Table2' ),
                            'Table2'[Base date] <= EARLIER ( Table2[Base date] )
                        )
                    )
                RETURN
                    _total,
            "_rolling2",
                VAR _total =
                    CALCULATE (
                        SUM ( Table2[Stock out] ),
                        FILTER ( ALL ( Table2 ), 'Table2'[Base date] <= EARLIER ( Table2[Base date] ) )
                    )
                RETURN
                    _total
        )
    )
VAR _rollingTotal_stock_In =
    MAXX (
        FILTER ( _table, 'Table2'[Base date] = SELECTEDVALUE ( Table2[Base date] ) ),
        [_rolling1]
    )
VAR _rollingTotal_stock_Out =
    MAXX (
        FILTER ( _table, 'Table2'[Base date] = SELECTEDVALUE ( Table2[Base date] ) ),
        [_rolling2]
    )
RETURN
    _rollingTotal_stock_In - _rollingTotal_stock_Out

Using this metric in table gives you the expected result of your scalar yellow:

Best Regards,

hackcrr

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

Hi Dor-Y13 

Thanks for the reply from hackcrr , please allow me to provide another insight:

Here I create a measure, it's easily to understand that the outcome is the result of the cumulative values of Stock In minus Stock Out:

MEASURE =
VAR _currentDate =
    MAX ( 'Table'[Base date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Stock In] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Base date] <= _currentDate )
    )
        - CALCULATE (
            SUM ( 'Table'[Stock Out] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Base date] <= _currentDate )
        )

 The result is as follow:

Best Regards

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