Circular Dependency in Power Bi calculated column

[dnd588]

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 Power BI data view. I have no. of daily purchase & sale units in the “number of units” column and daily purchase and sales amount in the “Market Value” column.

1. We need to calculate Changes in closing investment which is in case of

Sales Transaction = “Number of units with Direction” * “Previous Day NAV”

Purchase Transaction = Purchase/ Sale Value..

Daily Changes column will be used for calculating closing investments.

Changes in Cl invst = if('Investment Schedule'[Direction_4]=-1,'Investment Schedule'[Previous day NAV]*'Investment Schedule'[NOUWdirection],'Investment Schedule'[Purchase/ Sales Value]) 

2. Previous Day Weighted average cost is nothing else, but the last day weighted average cost

Previous day Weigh Avg Cost = 
Var Previousrow = CALCULATE(
    Max('Investment Schedule'[Rank]),
    Filter(
        ALLEXCEPT('Investment Schedule','Investment Schedule'[id_number]),
        'Investment Schedule'[Rank] < EARLIER('Investment Schedule'[Rank])
    ))
    Return
    CALCULATE(Sum('Investment Schedule'[Weigh Avg Cost]),FILTER(ALLEXCEPT('Investment Schedule','Investment Schedule'[id_number]),'Investment Schedule'[Rank] = Previousrow))
    //Previous day NAV is weighted average NAV of previous day.

3. Weighted Average cost = Closing investment/ Closing units

Weigh Avg Cost = 'Investment Schedule'[Closing Invst]/'Investment Schedule'[Closing units]
// It is divison of Closing investment by closing units.

4. For calculating the Closing units on the day end I have used the running total of “Number of units_with direction”

Closing units = SUMX (
    FILTER (
        'Investment Schedule',
        EARLIER ( 'Investment Schedule'[id_number]) = 'Investment Schedule'[id_number]
            && EARLIER ( 'Investment Schedule'[Rank]) >= 'Investment Schedule'[Rank]
    ),
    'Investment Schedule'[NOUWdirection]
)
//It is the runnning total of NOUWdirection

5. For calculating the Closing investment on the day end I have used running total of “Changes in Closing investment”.

Changes in Cl invst = if('Investment Schedule'[Direction_4]=-1,'Investment Schedule'[Previous day NAV]*'Investment Schedule'[NOUWdirection],'Investment Schedule'[Purchase/ Sales Value]) 

QUERY

I am having problem in calculating Changes in closing investment for day 2 & subsequent days as the input for calculating changes in closing investment is day 1 weighted average NAV which was calculated based on the changes in closing investment of Day 1 and causes a circular dependency. 

I am attaching a table with similar calculation & link for the powerbi report for your reference & to make it more clear. I am not able to compute daily changes in power bi but it can be computed in excel. 

Hoping for a positive response.

Thanks 

Link for powerbi report : https://drive.google.com/file/d/19Fps9BkRakwqYmr52-JDYpedumc69v-K/view?usp=drivesdk

Company Code	Transaction	Date	ID_number	Rank	Flow	Direction_4	Purchase/ Sales Value	No. of units	NOUW Direction	Closing units	Price per unit	Changes in Closing Investment	Closing Investment	Weighted Average Cost	Previous Day Weighted Avg Cost	Cost of investment sold	Profit	ROI
20001	1	01/04/2023	ADITYABMF	1	Purchase	1	100,000.00	1000	1000	1000	100.00	100,000.00	100,000.00	100.00	-	-	-	0.00%
20001	2	02/04/2023	ADITYABMF	2	Sale	-1	22,000.00	200	-200	800	110.00	(20,000.00)	80,000.00	100.00	100.00	(20,000.00)	2,000.00	10.00%
20001	3	03/04/2023	ADITYABMF	3	Purchase	1	31,500.00	300	300	1100	105.00	31,500.00	111,500.00	101.36	100.00	-	-	0.00%
20001	4	04/04/2023	ADITYABMF	4	Sale	-1	10,600.00	100	-100	1000	106.00	(10,136.36)	101,363.64	101.36	101.36	(10,136.36)	463.64	4.57%
20001	5	05/04/2023	ADITYABMF	5	Sale	-1	32,400.00	300	-300	700	108.00	(30,409.09)	70,954.55	101.36	101.36	(30,409.09)	1,990.91	6.55%
20001	6	06/04/2023	ADITYABMF	6	Purchase	1	30,900.00	300	300	1000	103.00	30,900.00	101,854.55	101.85	101.36	-	-	0.00%
20001	7	07/04/2023	ADITYABMF	7	Purchase	1	26,000.00	250	250	1250	104.00	26,000.00	127,854.55	102.28	101.85	-	-	0.00%
20001	8	08/04/2023	ADITYABMF	8	Sale	-1	75,600.00	700	-700	550	108.00	(71,598.55)	56,256.00	102.28	102.28	(71,598.55)	4,001.45	5.59%

[lbendlin]

What's NAV?

Can your problem's sample data be reduced to this?

A circular reference in DAX can usually be avoided by implementing the logic in Power Query instead.

[dnd588]

Hi @lbendlin 

We will also require Weighted Average NAV and daily changes column for further calculations.

NAV is value of each unit of investment. I am calculating weighted average NAV by dividing (Closing Investment) by (Closing no. of units).

[lbendlin]

I get rather different numbers:

"NAV cumul"  shows the NAV of the remaining units after each transaction.

[dnd588]

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%