Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 not summation.

 

In Gerhard's blog, he replied in one of the comments saying it "should be easy" but I haven't been able to figure it out for 3 days already.

 

I have gone through this thread but I don't understand what the user wrote, nor the answer derived.

 

Capture.PNG

 

My attempt is:

Forecast Inventory = 
VAR PrevInv = CALCULATE ( SUMX(Table, Table[Inventory]), DATEADD( DateTable[Dates], -1, MONTH))

RETURN 
     IF ( ISBLANK ( Table[Inventory] ) ,
           PrevInv - SUMX(Table, Table [Sales]), 
           SUMX(Table, Table [ Inventory] ) 
       

I know that the "PrevInv" variable is not calculated correctly but I really don't know how else to proceed. Can anyone please help?

 

Assume that a DateTable has been created separately and is configured properly.

 

Thank you!

15 REPLIES 15
Anonymous
Not applicable

Anyone? Please help...

Hi @Anonymous,

 

With the printscreen what are your expected outcomes?

 

Please give the dummy data( easy to manipulate, not a printscreen) as well as your expected results and then i can m ake a try...

 

Ninter

Anonymous
Not applicable

This is the expected outcome

This is the file with dummy data:

https://www.dropbox.com/s/wc30mt5kron382t/Inventory%20Recursive%20Calculation.pbix?dl=0

 

Capture.PNG

Hi @Anonymous,

 

Is this your expected result?  You may download the file from here.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish, thank you so much. 


What if I have a new inventory value in February, and I'm supposed to take that value for the DATESYTD calculation? Do I use the LASTNONBLANK function?

Hi,

 

I think my solution should still work.  Please try it.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish, no it doesn't, if I have another value for SKU A in February 2018 (say 400), your measure totals up the inventory values for the past two months. which means the remaining inventory for February will become 900 - (28 + 22) = 850.

 

The february inventory data should override the previous inventory data and the calculation should be carried forward. I.e. the february forecast value should be 400-22 = 378, and march forecast should be 378 - 16 = 362 etc etc.

 

(I'm trying to use :

CALCULATE ( SUM ( Inventory[Qty] ) , LASTNONBLANK(...))

 but I am still stumbling (I'm still quite new at DAX)).

 

Thank you!

 

inventory.PNG

Hi,

 

I cannot quite appreciate your example.  First, there should also be Purchases data. so that opening stock + Purchases - Sales = Clsoing stock.  In your example, the inv qty for Feb should be 472-22=450.

 

Am i incorrect?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Yes you're correct

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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, I have the same problem too. Can someone please help?

 

Thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.