Forum Discussion

aramirez2's avatar
aramirez2
Helper I
6 years ago
Solved

Predict Future Inventary

Hello.

 

I am creating Inventory Table mixing real data for the past days and predicted data for the future days. I have these 3 tables:

 

https://docs.google.com/spreadsheets/d/1NHSx1a4oN7X3hYK39o39RcaKuhZjYkLkh2OtDvnKx3o/edit#gid=0

 

Inventory Table Column Formulas are:

 

S0 = IF(inventory[date]<TODAY(), PastInventory [S0],CALCULATE(MIN(inventory[S_final]), ALLEXCEPT(inventory, inventory[sku]), PREVIOUSDAY(inventory[date])))
 
Incomes = IF(inventory[date]<TODAY(), PastInventory [Incomes], LOOKUPVALUE('Incomes'[quantity_in], 'Incomes'[product], inventory[product], 'Incomes'[date_in], inventory[date])
 
Outcomes = IF(inventory[date]<TODAY(), PastInventory [Outcomes], 
LOOKUPVALUE(PastInventory [Maxlast3dOutcomes], PastInventory [product], inventory[product]))
 
Sf = So + Incomes - Outcomes
 
My issue comes up with Sf formula as I fall down in a circular dependency error as Sf column is affected by "So" and "So" is based on last day Sf column. However no single row has circular dependency because "So" gets previous Sf row data and Sf is taking the same row data.
 
As you can see I was able to build up INVENTORY table with Excel without any dependency error because Excel works with row level and Power BI with column error. I do not know how I can avoid this circular depend on Power BI. 
 
May anyone help me please?
 
Thanks in advance.
 
 
Need Help
  • aramirez2's avatar
    aramirez2
    6 years ago

     

    v-lili6-msft  Sorry I forgot to open the spreadsheet. However I have already found the solution

    Sf = VAR Sf = LOOKUPVALUE(PASTINVENTORY[Sf], PASTINVENTORY[Date], MAX(PASTINVENTORY[Date]), PASTINVENTORY[Product], inventory[productId]) 
var curdate = inventory[date] 
var lastdate1 = MAX(PASTINVENTORY[Date]) 
var io = CALCULATE(SUM(inventory[income])-SUM(inventory[outcome]),FILTER(ALL(inventory),AND(inventory[date] <= curdate,inventory[date] >= lastdate1))) 
var a = IF(inventory[date]<TODAY(), LOOKUPVALUE(PASTINVENTORY[Sf], PASTINVENTORY[Date], inventory[date], PASTINVENTORY[Product], inventory[productId]), Sf+io) 
RETURN a

     

4 Replies