cancel
Showing results for
Did you mean:
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.

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

## Re: Inventory Forecast Recursive Calculation

Established Member

## Re: Inventory Forecast Recursive Calculation

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

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

Highlighted
New Member

## Re: Inventory Forecast Recursive Calculation

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

Thank you very much!

Super User

## Re: Inventory Forecast Recursive Calculation

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

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

## Re: Inventory Forecast Recursive Calculation

Hi,

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

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!

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?

Announcements

#### How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 1,297 guests
Recent signins: