Forum Discussion
Circular Dependency in Power Bi calculated column
Hi Community, I have been trying all sort of tricks with DAX, but just cannot get to a solution to my challenge. Would appreciate some help. I am trying to create an investment schedule in the Po...
lbendlin
Super User
Super UserI get rather different numbers:
"NAV cumul" shows the NAV of the remaining units after each transaction.
Hi lbendlin
For your convenience, I've written the formulas in each column. I am encountering a circular dependency while calculating "Changes in closing investments" for "02/04/2023". Since the formula for calculating "Changes in closing investments" for "02/04/2023" is "NOUW Direction * weighted average cost of 01/04/2023," and weighted average cost of 01/04/2023 is determined by dividing "Closing Investment by Closing Units of 01/04/2023,"
Regards and thanks
Durgesh Gupta
*NOUW = Number of Units With
| Company Code | Transaction | Date | ID_number | Flow | Direction_4 | Purchase/ Sales Value | No. of units | Price per unit | Rank | NOUW Direction | Closing units | Changes in Closing Investment | Closing Investment | Weighted Average Cost | Previous Day Weighted Avg Cost | Cost of investment sold | Profit | ROI |
| Formula | 1= Purchase -1 = Sales | No of units purchased or sold | No of units × Direction | Running total of "NOUW Direction" | In case of :- Purchase = Purchase/ Sales value Sales = Previous day weighted avg cost" × NOUW Direction | Running Total of "Changes in closing investment" | Divison of "Closing investment" by "Closing units" | Previous day "Weighted average cost" | "Previous day weighted avg cost" × "NOUW Direction" | "Purchase/Sales Value" - "Cost of investment sold" | Division of "Profit" by "Cost of investment sold" | |||||||
| 20001 | 1 | 01/04/2023 | ADITYABMF | Purchase | 1 | 100,000.00 | 1000 | 100.00 | 1 | 1000 | 1000 | 100,000.00 | 100,000.00 | 100.00 | - | - | - | 0.00% |
| 20001 | 2 | 02/04/2023 | ADITYABMF | Sale | -1 | 22,000.00 | 200 | 110.00 | 2 | -200 | 800 | (20,000.00) | 80,000.00 | 100.00 | 100.00 | (20,000.00) | 2,000.00 | 10.00% |
| 20001 | 3 | 03/04/2023 | ADITYABMF | Purchase | 1 | 31,500.00 | 300 | 105.00 | 3 | 300 | 1100 | 31,500.00 | 111,500.00 | 101.36 | 100.00 | - | - | 0.00% |
| 20001 | 4 | 04/04/2023 | ADITYABMF | Sale | -1 | 10,600.00 | 100 | 106.00 | 4 | -100 | 1000 | (10,136.36) | 101,363.64 | 101.36 | 101.36 | (10,136.36) | 463.64 | 4.57% |
| 20001 | 5 | 05/04/2023 | ADITYABMF | Sale | -1 | 32,400.00 | 300 | 108.00 | 5 | -300 | 700 | (30,409.09) | 70,954.55 | 101.36 | 101.36 | (30,409.09) | 1,990.91 | 6.55% |
| 20001 | 6 | 06/04/2023 | ADITYABMF | Purchase | 1 | 30,900.00 | 300 | 103.00 | 6 | 300 | 1000 | 30,900.00 | 101,854.55 | 101.85 | 101.36 | - | - | 0.00% |
| 20001 | 7 | 07/04/2023 | ADITYABMF | Purchase | 1 | 26,000.00 | 250 | 104.00 | 7 | 250 | 1250 | 26,000.00 | 127,854.55 | 102.28 | 101.85 | - | - | 0.00% |
| 20001 | 8 | 08/04/2023 | ADITYABMF | Sale | -1 | 75,600.00 | 700 | 108.00 | 8 | -700 | 550 | (71,598.55) | 56,256.00 | 102.28 | 102.28 | (71,598.55) | 4,001.45 | 5.59% |
| (132,144.00) | 8,456.00 | 6.40% |