Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Inventory Forecast Recursive Calculation

Hi all, I'm trying to create a DAX measure that populates "forecasted remanining inventory".   I have gone through Gerhard's blog on recursive calculation, but it talks about compound interest and ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

I fully understand.

 

1. On purchasing data: I am excluding it for the sake of simplicity. And not all products are bought every month.

2. On using 400 instead of 472: The reason why I used "400" (note that this is a manual input) is because there will be stock takes at the end of every month, and sometimes there will be discrepancies in the inventory. So my team decided that we should have a manual input of inventory at the end of each month.

 

So the objective is to take the latest inventory data available and then perform further forecasting, month-on-month.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

So inventory will be counted at the end of every month and will be manually input every month in Table.  Am i correct?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Yes you're correct

  • ICWiener's avatar
    ICWiener
    Frequent Visitor
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.