Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Circular Dependency - DAX

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 Shar...
Need Help
Show and Tell
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

     

    DateIncome Cap GainWITHDRAWN SHARESTOTAL SHARESADJUSTED TOTAL SHARES
          

    8/10/1988

    		  0.00200,000.000200,000.000
    9/31/19880.0000.000(826.72)200,000.000199,173.280
    9/30/19880.0000.000(799.74)199,173.280198,373.536
    10/31/19880.0000.000(784.68)198,373.536197,588.853
    11/30/19880.0000.000(786.16)197,588.853196,802.689
    12/31/19880.0000.000(784.68)196,802.689196,018.006
    1/31/19893058.2734050.693(809.06)203,126.972202,317.911
    2/28/19890.0000.000(823.45)202,317.911201,494.459
    12/24/19960.0000.000(807.49)201,494.459200,686.965
    12/24/19960.0000.000(774.47)200,686.965199,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's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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!