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

 

 

 

Need Help
Show and Tell

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft 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
        Microsoft 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