cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## Circular dependency in invetory calculate

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,

 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?

3 REPLIES 3
Community Support

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：

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.

Frequent Visitor

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

Super User

@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)))

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors