Rollover Function Needed Within Matrix

[marksaba]

I was recently assisted on this post Rollover Function Needed Within Matrix and was able to put together the below Measure to create an inventory walk by month.

Net Inventory WALK = 
VAR _t = 
ADDCOLUMNS(
    WINDOW(
        1,
        ABS,
        0,
        REL,
        ALL('Date Table'[Month Yr Sorted],'Date Table'[Sort]),
        ORDERBY('Date Table'[Sort],ASC)
    ),
    "@currentmonth",
    CALCULATE(
        [Stock On Hand],
        KEEPFILTERS('Date Table'[End of Month]=EOMONTH(TODAY(),0))
    ),
    "@receipt",[Total Receipts],
    "@demand",[Total Demand]
)
RETURN
SUMX(_t,[@currentmonth]+[@receipt]-[@demand])

However, my goal is to represent the Net Inventory WALK value as shown in the highlighted bottom row of the table (named "Desired Net Inventory WALK"). In this adjustment, it disregards when the Net Inventory for a month is Negative and makes the starting inventory for the next month 0. Therefore, Net Inventory for Jan-May 2024 should each be 0, then Jun 2024 at 2685, etc (as shown in Desired Net Inventory WALK).

Does anybody know how to accomplish this?

[amitchandak]

@marksaba , For on hand inventory you can have measures like

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))

You can use window for cumulative like

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[marksaba]

Thank you for the suggestion but this isn't working. i'd like to continue using the original function but add a layer in it that can factor for the negatives and assign them as zero.

For reference, the Stock on Hand value used in the function is a current stock on hand. The Stock on Hand listed in the table is a separate measure, which takes the Net Inventory WALK value from the prior month.