March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
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.
This is what I am looking for. Thank you very much Liu Lang
@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)))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
84 | |
82 | |
74 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |