Circular Dependency - DAX

[sv12]

Hi

I am trying to recreate an excel calculation in Power BI.

In excel - I have 2 columns denpendent on each other in the calculations.

"Adjusted total Shares" is dependent on column "Total Shares". AND "Total Shares" column is dependent on previous value of "Adjested Total Shares". Please refer to the Excel formula in the attached data link.

Data File: https://drive.google.com/open?id=1YdxP-yv9C7tP3B1zo3tIvs8GCFWr-6M2

I am trying to recreate these 2 as measure/calculated columns in Power BI.

Any help to solve this issue would be appreciated!!

Thanks

[v-yulgu-msft]

Hi @sv12 ,

What is the source table structure? Are "Adjusted total Shares" and "Total Shares" calculated based on original feilds? Please provide more description about the calculation regular with examples.

Best regards,

Yuliana Gu

[sv12]

@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

[v-yulgu-msft]

Hi @sv12 ,

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

[sv12]

@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!