Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Rollover Function Needed Within Matrix

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 = AD...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous , 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.