Rolling balance of inventory projection

[cyuan]

Hi all,

I am working on a measure to present the closing balance of inventory on SKU level

basica logic is SOH-residual balance of forecast+inbounds on monthly basis:

The DAX i had give me below result

ATP =

var _Inbounds=calculate([Inbounds],filter(all('01 Calendar'[Date]),'01 Calendar'[Date]<=MAX('01 Calendar'[Date])))

var _FC=calculate([Current FC],filter(all('01 Calendar'[Date]),'01 Calendar'[Date]<=MAX('01 Calendar'[Date])))

return

calculate([SOH_FULL]-IF(_FC>=[Actual],_FC-[Actual],0)+_Inbounds,filter('01 Calendar',min('01 Calendar'[Date])>=DATE(YEAR(TODAY()),1,1)))

It should be 

	SOH_Full	Inbounds	Aactual	Current FC	ATP	Formula
Sep-24	8331		1107	1990	7448	=B2+C2+IF(E2<D2,0,E2-D2)
Oct-24				1560	5888	=F3-E2+C2
Nov-24				1883	4005	=F3-E4+C4
Dec-24				2337	1668	=F4-E5+C5
Jan-25				1567	101
Feb-25		6000		1371	4730
Mar-25				2010	2720
Apr-25				2083	637
May-25				1707	-1070
Jun-25				1675	-2745
Jul-25				1608	-4353
Aug-25				2334	-6687
Sep-25				2062	-8749

In addition, as above is on SKU level, I also need a view of product family level which means sum up all postive numbers/zero out all negative month over month.

Highly appreciate if your help in advance.

Chen

[DataNinja777]

Hi @cyuan ,

My concern with summing only the positive values and zeroing out the negative ones is that it will consistently overstate the inventory in the model. 

Best regards,

[cyuan]

Hi ninja,

thanks reply. in my case forecast will expire month over month, hence no backorder qty should be counted in, once inventory balance is negative or zero, new month forecast should also be zero out too because nothing we could supply such that inventory will not be inflated. 

hope it makes sense.

CY