cancel
Showing results for
Search instead for
Did you mean:
johnconnor92 Regular Visitor

## Re: Inventory Forecast Recursive Calculation

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. Super User

## Re: Inventory Forecast Recursive Calculation

Hi,

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

johnconnor92 Regular Visitor

## Re: Inventory Forecast Recursive Calculation

Yes you're correct

ICWiener Frequent Visitor

## Re: 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 Super User

## Re: Inventory Forecast Recursive Calculation

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.

johnconnor92 Regular Visitor

## Re: Inventory Forecast Recursive Calculation

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.

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 48 members 1,200 guests
Recent signins:
• vpatel55 • • torsten • SaiSrinivas17 • mariner84 • depple • mrao Please welcome our newest community members:
• ldphong • BartoszB • metrowall • rizzotony • JennyJun9999 • Paule • maha_gandhan 