Predict Future Inventary

[aramirez2]

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.

[v-lili6-msft]

hi  @aramirez2 

The file you uploaded needs permission, please upload again.

and you could also have a look this blog:

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Regards,

Lin

[aramirez2 · Accepted solution]

@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

[Greg_Deckler]

@aramirez2 - Perhaps have a look at Days of Supply - https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

[aramirez2]

Thanks @Greg_Deckler  for your answer. It is a great exercise even though it is not very related to my issue.