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

Highlighted
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

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.

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?

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,297 guests
Please welcome our newest community members: