Inventory Forecast Recursive Calculation

Hello,
I'm the user from this topic, replying to your PM. Indeed it looks like the problem i was having. It took steps but I think I managed to get what you needed ?

1. Create the date MEASURE 

get date = max('DateTable'[Date])

2. Create the Sales At Date MEASURE (everything will be done by measures, not calculated columns) 

SalesAtDate = calculate(sum(Table2[Sales Forecast]);filter(Table2;Table2[Month]=[get date]))

3. Same for Inventory :

InventoryAtDate = calculate(sum(Table2[Inventory]);filter(Table2;Table2[Month]=[get date]))

4. Create the measure of inventory per month 

Inventory In Month = 
	Calculate(
		sumx( values('DateTable'[Date]); [InventoryAtDate]);
		datesbetween(
				'DateTable'[Date];STARTOFMONTH('DateTable'[Date]);ENDOFMONTH('DateTable'[Date])))
	

5. And its last non blank value : 

Inventory Last non blank = LASTNONBLANK(Feuil1[Inventory];1)

5. Create the measure with the sales per day for days where there is no inventory data : 

Sales Last Non blank = if(isblank([Inventory In Month]);[SalesAtDate];blank())

6. And sum this measure (that way it will not start from january, it will start as soon as you don't have an inventory)

SalesCumulative = 
	Calculate(
		sumx(values('DateTable'[Date]); [Sales Last Non blank]);
		datesbetween('DateTable'[Date];bLANK();max('DateTable'[Date]))
	)

7. The forecast is the substraction of Cumulated sales from the last inventory date and the last inventory

Inventory Forecast = if(isblank([Inventory In Month]);CALCULATE([Inventory Last non blank] - [SalesCumulative]) ;[Inventory In Month])

 Hope that does the trick. It is surely not the best way to do it, i'm still new at this.

Cheers

Hi,

If inventory will be manually input every month and there is also sales for every month, then the invenory will simply be

=[inv qty]-[sales qty]

where inv qty and sales qty are simple SUM() measures.

Hi Ashish,

I hope you can tell from icwiener's post that it really isn't so straightforward. The measure you suggested will not work for future months where there is no value for the inventory. that's why I'm trying to figure out a measure to calculate the forecasted inventory value.