I am working on Cost of goods sold using Powerbi.
https://www.mediafire.com/file/pmb7u1thsag1kq1/Cost+of+goods+sold.pbix/file
Above is my file which i uploaded on mediafire.
i am taking the average price by using average function year wise. If you see in File.
| GName | Year | Opening Stock | InQty | OutQty | InItemValue | Average Value | Closing Stock | Closing Stock Value | Opening Stock Value | Cost of Goods Sold |
|-------------|------|---------------|-------|--------|-------------|---------------|---------------|---------------------|---------------------|--------------------|
| Bahria Town | 2016 | | 4454 | 3586 | 126610299.8 | 28426.20113 | 868 | 24673942.58 | 0 | 101936357.2 |
| Bahria Town | 2017 | 868 | 6379 | 6547 | 166903971.5 | 23030.76743 | 700 | 16121537.2 | 0 | 150782434.3 |
| Bahria Town | 2018 | 700 | 9129 | 8709 | 271932546.3 | 27666.3492 | 1120 | 30986311.11 | 0 | 240946235.2 |
| Bahria Town | 2019 | 1120 | 9333 | 9393 | 313226466.8 | 29965.22212 | 1060 | 31763135.45 | 0 | 281463331.4 |
| Bahria Town | 2020 | 1060 | 10192 | 10136 | 362950101.2 | 32256.49673 | 1116 | 35998250.35 | 0 | 326951850.8 |
| Bahria Town | 2021 | 987 | 8882 | 8468 | 404199067.4 | 40956.43605 | 1530 | 62663347.16 | 0 | 346819100.5 |
In Above as you can see i just took the Average Value
Average Value = ([Opening Stock Value]+[initemvaluee])/([inqtyy]+[Opening Stock])
and closing stock value
Closing Stock Value = [Average Value] * [Closing Stock]
When i calculate the closing stock value of previous year its give me error.
circular dependency was detected: Measure: 'mak_stockInHandValue'[Average Value], Measure: 'mak_stockInHandValue'[Opening Stock Value], Measure: 'mak_stockInHandValue'[Average Value].
Any Suggestion to see the closing stock value in the field of opening stock value ?????
I am working on this from more than 2 weeks.
Please help me out
Thanks in advance
