Circular dependency in invetory calculate

[William_Moreno]

Hello everyone,
I need to build a formula in the powerbi to calculate the openning inventory, but if the date is not the same as today, this should consider t of closing inventory of the previous day.
When I try to do this the powerbi mesage is circular dependecy,
Below follow a example:

Unit	Product	Date	Openning Inventory	Manufacturing product	Sales	Closing Inventory
Dallas	XFG044	21/12/2020	792	180	10	962
Dallas	XFG044	22/12/2020	962
Dallas	XFG044	23/12/2020
Dallas	XFG044	24/12/2020
Dallas	XFG044	25/12/2020

Can someone help me?
Thanks in advanced.

[Anonymous]

Hi  @William_Moreno ,

I customize the data of manufacturing products and sales：

Here are the steps you can follow：

1. Enter power query through transform data and select add column --- index column --- from 1 to generate the index

2. Create Measure.

Closing Inventory_measure =
var _index=MAX('Table'[Index])
var _1=CALCULATE(SUM('Table'[Openning Inventory]),FILTER(ALL('Table'),[Index]<=_index))
var _2=CALCULATE(SUM('Table'[Manufacturing product]),FILTER(ALL('Table'),[Index]<=_index))
var _3=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[Index]<=_index))
var _result=_1+_2-_3
return
IF(MAX('Table'[Index])=1,962,_result)

Openning Inventory_measure =
var _1=
CALCULATE([Closing Inventory_measure],FILTER(ALL('Table'),[Index]=MAX([Index])-1))
return
IF(MAX('Table'[Index])=1,792,_1)

3. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ExcelHent1Henry]

This is what I am looking for.  Thank you very much Liu Lang

[amitchandak]

@William_Moreno , Try meausre like

Openning Inventory =
var _min = minx(all(Table), Table[Month]) //or use date
return
calculate(sum(Table[Openning Inventory]), filter(Table, Table[date] =_min)) - calculate(sum(Table[ Manufacturing])-sum(Table[product Sales]), filter(Table, Table[date] <max(Table[date)))

Openning Inventory =
calculate(sum(Table[Openning Inventory]), filter(Table, Table[date] =calculate(min(Table[date]), allexpcept(Table,table[Product]))) - calculate(sum(Table[ Manufacturing])-sum(Table[product Sales]), filter(Table, Table[date] <max(Table[date)))