Qty Roll Up from Row Qtys

LGS — Mon, 31 Jul 2023 18:27:56 GMT

Hi. New Poster Here looking for some assistance. Hopefully, it is something simple.

Problem Summary: Attempting to run a forecast model using current inventory qtys, adding in incoming purchases (using arrival due date related to calendar), and taking away forecasted demand for each month. The one thing we missed is the backorder qty. While it is captured in the formula, the miss is we need to decrement the backorder from the projected inventory and show the backorders on a separate line.

Formula being used:

Potential Backorder =

var maxdate = MAX('Inventory Report'[Data_Upload_Date])

var invqty = CALCULATE(SUM('Inventory Report'[Qty OH]), 'Inventory Report'[Data_Upload_Date]=maxdate)

var currentmonth = DATE(YEAR(TODAY()),MONTH(TODAY()),1)

var test = invqty + CALCULATE([Incoming Inventory - Demand Delta]), FILTER(ALL('Calendar'), AND('Calendar'[Date]>=currentmonth,'Calendar'[Date]<=MAX('Calendar'[Date]))))

return if(ISFILTERED('Widget Master'[Widget]),if(test>=0, 0, test),

SUMX(SUMMARIZE('Widget Master', 'Widget Master'[Widget], "BO Proj", if(test>0,0, test)),SUM([BO Proj))

)

This is what I want:

This is what I am getting:

The above tables are roll ups of Widgets. The breakdown table would look like this and it does when I drop in the Widgets but I loose the Widget detail when I remove the Widgets:

What I want:

What I get:

I appreciate any insight.

LGS

topic Qty Roll Up from Row Qtys in DAX Commands and Tips

Qty Roll Up from Row Qtys