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.
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...
This is the expected outcome
This is the file with dummy data:
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 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)).
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?