Forum Discussion
Circular Dependency - DAX
v-yulgu-msft Thank you for your response.
Below, the Red Highlighted part is the source data. Black Highlighted are to be calculated.
The formula for Total Shares in Excel:
If( Date = 8/10/1988, 200000, Income + Cap Gain+ Previous Values of Adjusted Total Shares)
Formula for Adjusted Total Shares:
Total Shares + Withdrawn shares
There is a circular dependancy on these 2 columns.
| Date | Income | Cap Gain | WITHDRAWN SHARES | TOTAL SHARES | ADJUSTED TOTAL SHARES |
8/10/1988 | 0.00 | 200,000.000 | 200,000.000 | ||
| 9/31/1988 | 0.000 | 0.000 | (826.72) | 200,000.000 | 199,173.280 |
| 9/30/1988 | 0.000 | 0.000 | (799.74) | 199,173.280 | 198,373.536 |
| 10/31/1988 | 0.000 | 0.000 | (784.68) | 198,373.536 | 197,588.853 |
| 11/30/1988 | 0.000 | 0.000 | (786.16) | 197,588.853 | 196,802.689 |
| 12/31/1988 | 0.000 | 0.000 | (784.68) | 196,802.689 | 196,018.006 |
| 1/31/1989 | 3058.273 | 4050.693 | (809.06) | 203,126.972 | 202,317.911 |
| 2/28/1989 | 0.000 | 0.000 | (823.45) | 202,317.911 | 201,494.459 |
| 12/24/1996 | 0.000 | 0.000 | (807.49) | 201,494.459 | 200,686.965 |
| 12/24/1996 | 0.000 | 0.000 | (774.47) | 200,686.965 | 199,912.492 |
With the Red highlighted part as raw data in Power BI, I am trying to replicate these calculations in Power BI.
Please let me know what you think.
Thanks
Microsoft EmployeeHi Anonymous ,
You may try below calculated columns.
TOTAL SHARES =
VAR previous_TotalShares =
CALCULATE (
SUM ( Sample3[ADJUSTED TOTAL SHARES] ),
FILTER ( Sample3, Sample3[Index] = EARLIER ( Sample3[Index] ) - 1 )
)
RETURN
IF (
Sample3[Date] = DATE ( 1988, 8, 10 ),
200000,
Sample3[Income ] + Sample3[Cap Gain] + previous_TotalShares
)
ADJUSTED TOTAL SHARES =
200000
+ CALCULATE (
SUM ( Sample3[WITHDRAWN SHARES] ),
FILTER ( Sample3, Sample3[Index] <= EARLIER ( Sample3[Index] ) )
)
Best regards,
Yuliana Gu
v-yulgu-msft Thanks for your response.
The "Withdrawn Shares" data is actually a Measure (with What-if Parameters). I did not mention that to simplify the problem.
But when I try to use the What-If Parameters enabled measure value (Withdrawn Shares), with your code; the values are thrown off because What-if Parameters are not supported in Calculated columns!Is there a way to modify your code and use it for Measures instead of calculated columns?
Also, in your code: Please see the Red highlighted part. When I try the edit, the same cirlular dependancy error is been shown.
ADJUSTED TOTAL SHARES = 200000 (# this has to be replaced by If(Date = min(Date), 200000, current value of Total Shares)) + CALCULATE ( SUM ( Sample3[WITHDRAWN SHARES] ), FILTER ( Sample3, Sample3[Index] <= EARLIER ( Sample3[Index] ) ) )Thanks for looking into it!