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

New Member

## Re: Inventory Forecast Recursive Calculation

Thank you very much!

Super User

Highlighted
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

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 230 members 2,794 guests
Recent signins: