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

[naidu_sandhya]

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!

[v-binbinyu-msft · Accepted solution]

Hi @naidu_sandhya ,

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.