cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnconnor92 Regular Visitor
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
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
Regular Visitor

Re: Inventory Forecast Recursive Calculation

Yes you're correct

ICWiener Frequent Visitor
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
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
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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 148 members 2,109 guests
Please welcome our newest community members: