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

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
johnconnor92 Regular Visitor
Regular Visitor

Re: Inventory Forecast Recursive Calculation

Anyone? Please help...
Interkoubess Established Member
Established Member

Re: Inventory Forecast Recursive Calculation

Hi @johnconnor92,

 

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

johnconnor92 Regular Visitor
Regular Visitor

Re: Inventory Forecast Recursive Calculation

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

janesmith New Member
New Member

Re: Inventory Forecast Recursive Calculation

Hi, I have the same problem too. Can someone please help?

 

Thank you very much!

Super User
Super User

Re: Inventory Forecast Recursive Calculation

Hi @johnconnor92,

 

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/
johnconnor92 Regular Visitor
Regular Visitor

Re: Inventory Forecast Recursive Calculation

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?

Super User
Super User

Re: Inventory Forecast Recursive Calculation

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
johnconnor92 Regular Visitor
Regular Visitor

Re: Inventory Forecast Recursive Calculation

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

Super User
Super User

Re: Inventory Forecast Recursive Calculation

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/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)