Circular Dependency - Only calculate rows before current row

[shawnprodgers]

Hello Power BI Community,

I have an model where we are trying to calculate contract optimization where we take all transactions and compare them against alternatives. When analyzing these alternatives, we look at volume (A) on a MTD and YTD basis by supplier, where the MTD/YTD dates are all dates before the date of the transaction or, alternatively, the RowIndex before the transaction's RowIndex. Then, after analyzing A, if conditions are met (B) the report will say whether the original transaction supplier was the best option or if we should have selected a different supplier (C). Because of the MTD and YTD criteria, if we suggest the supplier is different, we want to track how much volume we are changing from the original supplier to the new supplier (if C is not the original supplier, flag as changed volume (D)). To properly calculate MTD and YTD on the conditions met column (B), we then have to reference A (original volume by supplier) and D (changed volume by supplier) but we only want the totals for all rows before the current row we are trying to calculate. However, we get a circular dependency on a columnar level, because we have a calculation step that looks like A > B > C > D > B. We tried things like CALCULATE(CALCULATE(sum(Volume),Supplier=earlier(Supplier)),RowIndex<earlier(RowIndex) as well as trying to emulate Dany Hoter's LookUpValue solution, but to no avail so far. All described steps are calculated columns as analysis is done at a row (transactional) level. Any insight would be very helpful and appreciate your time,

Shawn

[Eric_Zhang]

@shawnprodgers

Your post is a little confusing, could you post any sample or upload the pbix, do mask sensitive data before uploading, you can upload it to Onedrive or any other web storage.

[Eric_Zhang]

@shawnprodgers

Thanks for sharing the pbix. The formula looks good. Not sure what did you do, however when I create a new column with the exactly same formula, the circular dependency error doesn't occur and the output seems good.

For readability, you can format the the DAX formular on DAX formatter.

Condition Volume Total =
[Volume]
    + CALCULATE (
        SUM ( Test1[Volume] ),
        FILTER (
            ALL ( Test1 ),
            AND (
                Test1[Contract] = EARLIER ( Test1[Contract] ),
                Test1[Index.1] < EARLIER ( Test1[Index.1] )
            )
        )
    )
    + CALCULATE (
        CALCULATE ( SUM ( Test1[Qualified Volume] ), Test1[Condition Positive] = "Yes" ),
        FILTER ( ALL ( Test1 ), Test1[Index.1] < EARLIER ( Test1[Index.1] ) )
    )

Understanding Circular Dependencies for your reference.

[shawnprodgers]

Hi Eric,

Thanks for taking a look. I agree the formula works as is. The central issue I have is I need to reference the total of the formula in the Condition Volume Total column so when I do the Condition Test formula, it is calculating on the desired volume total. Even if I create a new column with the stated functioning formula, when I try to reference is in the Conditional Volume Total column, the circular reference returns and I'm back to square one. Any ideas?

Appreciate your time,

Shawn

[shawnprodgers]

Hi Eric,

Thank you for your response. Attached is a model that has two tables. One is the desired outcome, the other is the same table with fact columns and calculated columns trying to get the desired outcome with the circular dependency present. The key field is the Condition Volume Total and the key ability is to be able to include the volume that has met a condition into the net total of volume in the Condition Volume Total field. We can't use measures given that each row must be analyzed individually so unless we created a report table that shows every row (which obviously isn't ideal), we want to accomplish this with calculated columns.

Herer is the One Drive link: https://1drv.ms/u/s!AgADk3HM2f_cki_o1vMGVr4Dk6Nh

Really appreciate your time,,

Shawn