How to make the Stock inventory log list as my requirement (Inventory on hand)

[leafbear024]

Inventory on Hand is a manual table calculate as below formula: 

Inventory on Hand = CALCULATE (SUM ( ItemInOutHistoryList[No_of_Rolls] ),ItemInOutHistoryList[Posting_Date],

DATESBETWEEN (ItemInOutHistoryList[Posting_Date],DATE ( 2021, 01, 01 ),MINX (ALLSELECTED ( ItemInOutHistoryList ),'ItemInOutHistoryList'[Posting_Date]) - 1))

I would like to create a report as below :

Now my output is like that:

Posting Date	item_No	Inventory on Hand	No of Rolls
07/31/2021	M0001	36	-12
08/06/2021	M0001	36	-14
08/09/2021	M0001	36	-4
08/10/2021	M0001	36	-6
Total		36	-36

Expected output is like that:

Posting Date	item_No	Inventory on Hand	No of Rolls
07/31/2021	M0001	36	-12
08/06/2021	M0001	24	-14
08/09/2021	M0001	10	-4
08/10/2021	M0001	6	-6
Total		0	-36

how can I save the previous value and carry it to next day ....

[v-kkf-msft]

Hi @leafbear024 ,

Is your model like this?

Then try the following formula:

Measure = 
var Pre = 
    CALCULATE(
        SUM(ItemInOutHistoryList[No of Rolls]),
        FILTER(
            ALLSELECTED(ItemInOutHistoryList[Posting Date]),
            ItemInOutHistoryList[Posting Date] < MAX(ItemInOutHistoryList[Posting Date])
        )
    )
return 
    IF(
        ISFILTERED(ItemInOutHistoryList[Posting Date]),
        SUM('Inventory on Hand'[Inventory on Hand]) + Pre,
        SUM('Inventory on Hand'[Inventory on Hand]) + SUM(ItemInOutHistoryList[No of Rolls])
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[leafbear024 · Accepted solution]

Thanks for reply.  I will try to use your solution later :D.

However,  I finally solve this problem as below:

Inventory on Hand = CALCULATE(SUM('ItemInOutHistoryList'[No_of_Rolls]),FILTER('ItemInOutHistoryList','ItemInOutHistoryList'[Location_Code] = EARLIER('ItemInOutHistoryList'[Location_Code]) && 'ItemInOutHistoryList'[Item_No]=EARLIER('ItemInOutHistoryList'[Item_No]) && 'ItemInOutHistoryList'[Posting_Date].[Date]<= EARLIER('ItemInOutHistoryList'[Posting_Date].[Date]) - 1))

Result as below:

[amitchandak]

@leafbear024 ,a new column like

new column = [Inventory on Hand] + sumx(filter(Table, [item_no] = earlier([item_no]) && [Posting_date] = earlier([posting_date])), [No of Rolls])