Forum Discussion

sandyn-2303's avatar
sandyn-2303
Frequent Visitor
2 years ago
Solved

Calculating difference between Inventory for current month and forecast for next months

Hello,

 

I need help with creating a table like below. It need to get remaining stock in inventory every month till it goes negative. I have given the example below. If current month is August, I take difference between current month inventory and next month sale forecast to get the remaining stock. That is 49218 - 13378 = 35840. For Nov, I take difference from the remaining stock to subsequent month sale Forecast. That is 35840 - 14182 = 21658. I want to do this till I my stock goes negative.

I have tried multiple ways but have not been successful. Any help is much appreciated!

 

Thanks!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi sandyn-2303 ,

    Please try to create a new column with below dax formula:

     

    Remaining Stock =
    VAR _inventory =
        MAXX ( 'Table', [Inventory] )
    VAR _date = [Date]
    VAR tmp =
        FILTER ( 'Table', [Date] <= _date )
    VAR _a =
        SUMX ( tmp, [Forecast Sale] )
    RETURN
        _inventory - _a
    

     

    Please refer the attached .pbix file

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi sandyn-2303 ,

    Please try to create a new column with below dax formula:

     

    Remaining Stock =
    VAR _inventory =
        MAXX ( 'Table', [Inventory] )
    VAR _date = [Date]
    VAR tmp =
        FILTER ( 'Table', [Date] <= _date )
    VAR _a =
        SUMX ( tmp, [Forecast Sale] )
    RETURN
        _inventory - _a
    

     

    Please refer the attached .pbix file

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.