Creating a Aggregated Inventory measure

[Dor-Y13]

Hello,

i am trying to create a report for my inventory movements, i would like to know the exact balance of inventory for each date that has Inventory move.

please see table below for clarification purposes (i am computing a PBI report off course):

the desired measure is the Yellow one, which shows the inventory balance after the movements of the same row (with respect to the remaining balance before this date).

Could you please help me with the desired DAX function and what i am missing?

Thanks a lot in advance

[hackcrr · Accepted solution]

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.

[v-zhengdxu-msft · Accepted solution]

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.

[Dor-Y13]

Hello, sorry for the so late respone. 

I did like the simple version of this DAX, however it does not get me to the end result needed (I get really high figures on the new Measure, these numbers are not realistic so there is some kind of a problem in the calculation).

Could you might help me how to figure out where the high numbers come from? i am working on an ERP that i cannot upload a file for reference, however i will be happy to solve that issue.

Thanks a lot

[hackcrr · Accepted solution]

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.