Avoid Circular Dependency when evaluating previous rows.
Hello All, I am trying to create a power bi to analyse loan options. I have the following measures: Opening Balance, Monthly Instalments, Capital Repaid and Closing Balance Interest Charged. I am having a circular dependency error because: Interest Charged is calculated using the opening balance (Interest Charged = Opening Balance * Interest Rate) Capital Repaid is calculated using Monthly Instalments and Interest Charged (Capital Repaid = Monthly Instalments - Interest Charged ) Opening Balance is calculated using the cumulative of Capital Repaid evaluated in all previous rows (Opening Balance = Original Loan - Sum(all previous Capital Repaid) I do understand why that causes an error. I am just wondering how could I avoid the error. In my mind, there should be away since the circular dependency only occurs if Opening Balance uses the same row data when evaluating Capital Repaid. So far this is what I have: _Interest Charged = [_OpeningBalance]*[x_piRate]/1200 _Capital Repaid = MonthAmort[_Monthly Installements] - MonthAmort[_Interest Charged] _OpeningBalance = _Loan[Loan Value] - CALCULATE ( SUMX ( MonthAmort, [_Capital Repayment] ), FILTER ( MonthAmort[paymentDate], MonthAmort[paymentDate] < MAX ( MonthAmort[paymentDate] ) ) ) I got this down easily in excel, since you can evaluate the previous row quite straight forward: Repayment Number Opening Balance Monthly Installments Interest Charged Capital Repaid Closing Balance 1 540,000.00 2,332.37 1,435.50 896.87 539,103.13 2 539,103.13 2,332.37 1,433.12 899.25 538,203.88 3 538,203.88 2,332.37 1,430.73 901.64 537,302.24 4 537,302.24 2,332.37 1,428.33 904.04 536,398.20 5 536,398.20 2,332.37 1,425.93 906.44 535,491.75 6 535,491.75 2,332.37 1,423.52 908.85 534,582.90 7 534,582.90 2,332.37 1,421.10 911.27 533,671.63 8 533,671.63 2,332.37 1,418.68 913.69 532,757.94 9 532,757.94 2,332.37 1,416.25 916.12 531,841.82 10 531,841.82 2,332.37 1,413.81 918.56 530,923.26 The reason I am using measures, in case it is relevant, is because I want to dynamically change the Loan, Rate and Loan Period base on "What If" sliders. In that way, I can dynamically explore different scenarios. Hopefully, it is something that can be done. Any idea?
A circular dependency has been detected adding one column to a calendar table
Hello!! I have a large database of animals on a farm. I have the following measure to make the inventory that works for me however it takes a long, long time to finish. I had plans to put this formula in the PBIx calendar table adicinal column and thus have the values already calculated in a table in the hope of reducing time in inventory calculations and subsequent formulas with this data. I don't know if I'll succeed. However, when I place a column with the formula below in the calendar table with this measure, I get the error : A circular dependency has been detected: Calendar[Column]. Do you think it's possible to transform this DAX formula so that it doesn't have the circular dependency? I'd like to avoid making another inventory table, that works, although when related to the calendar table (1-1) it gives the same circular dependency error. And I´ll get a lot of difficulties in fixing all relationships.. I'm really at a blind spot with this step. Thank you Best regards INVENTÁRIO PORCAS = VAR MatrizesEntradas = CALCULATE( CALCULATE( COUNT(SOWS[Mating]), USERELATIONSHIP('Calendar'[Date], SOWS[Mating]), USERELATIONSHIP('FARM SETTINGS'[SiteId], SOWS[SiteId]) ), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ), USERELATIONSHIP('Calendar'[Date], SOWS[Mating]) ) VAR MatrizesSaidas = CALCULATE( CALCULATE( DISTINCTCOUNTNOBLANK(CYCLES[ID&FARM]), NOT(ISBLANK(CYCLES[Dia Saida])), USERELATIONSHIP('Calendar'[Date], CYCLES[Dia Saida]) ), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) ) RETURN CALCULATE( MatrizesEntradas - MatrizesSaidas, FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
Circular dependency was detected on calculated column in source table
Dear All, I have my source which is a semantic model from the existing workspace. The source for this model is SQL server. I don't know if this is important, but I would like to present you the background. I tried to add to one of my tables (Person) one new column based on CALCULATE, but I got an error: I was surprised to see that so I created a new table that is fully equal to Person (Table = Person). With this additional table, I don't get the circular dependency error when I'm adding a new column - no error was detected. Column Id contains only unique values. Could anyone advise on the reason why the expression works in one table and doesn't work in the other? Thank you in advance!Measure that dynamically calculates average cost (avoiding circular dependency)
I imported an Excel table with thousands of purchases and sales transactions of shares of stock. The imported data: traded tickers, transaction dates, number of traded shares, types of trade (purchase or sale), and traded share prices. I then calculated a measure that manages to tally the running Shares balance after each purchase/sale transaction occurs under each row of data. 3 other measures are also easily calculated: - Purchase total: Traded (purchased) shares x Share price - Total revenue: Traded (sold) shares x Share price - Total cost: Traded (sold) shares x Average cost I then need to calculate the last measure being Average cost, and that's where I get stuck with a circular dependency issue... The rationale behind this calculation is as follows: - If the transaction is a sale, just pick up the prior Average cost from the same column. - But if the transaction is a purchase, then [ALL Purchase totals up to the respective date for the respective ticker + ALL Total cost (negative) BEFORE the respective date for the respective ticker] DIVIDED BY the shares balance at the respective date. The below screenshot tries to illustrate everything described above (I hope it doesn't end up providing a confusing picture...): Since the actual code is pretty lengthy I'll split it up into 4 sections to make it easier to comprehend. 1) The prior Average cost picked up from the same column is computed by the following code: Average cost = VAR Avg_Cost_Tbl = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( fTrans, fTrans[Ticker], fTrans[Date] ), "@AvgCost", [Average cost] ), fTrans[Ticker] = fTrans[Ticker], fTrans[Date] <= fTrans[Date] ) VAR Prior_Avg_Cost = FILTER( Avg_Cost_Tbl, SELECTCOLUMNS( OFFSET( -1, Avg_Cost_Tbl, ORDERBY( fTrans[Ticker], ASC, fTrans[Date], ASC ) ), [@AvgCost] ) ) 2) ALL Purchase totals up to the respective date for the respective ticker is calculated by the following code: VAR Purch_Total_Tbl = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( fTrans, fTrans[Ticker], fTrans[Date], fTrans[Transaction] ), "@PurchTotal", [Purchase total] ), fTrans[Ticker] = fTrans[Ticker], fTrans[Date] <= fTrans[Date], fTrans[Transaction] = "Purchase" ) VAR Purch_Total_Sum = IF( HASONEVALUE( fTrans[Ticker] ), SUMX( WINDOW( 1, ABS, 0, REL, Purch_Total_Tbl, ORDERBY( fTrans[Date], ASC ), , PARTITIONBY( fTrans[Ticker] ) ), [@PurchTotal] ) ) 3) ALL Total cost PRIOR to the respective date for the respective ticker is calculated by the following code: VAR Total_Cost_Tbl = CALCULATETABLE( ADDCOLUMNS( SUMMARIZE( fTrans, fTrans[Ticker], fTrans[Date], fTrans[Transaction] ), "@Total_Cost", [Total cost] ), fTrans[Ticker] = fTrans[Ticker], fTrans[Date] <= fTrans[Date], fTrans[Transaction] = "Sale" ) VAR Total_Cost_Sum = IF( HASONEVALUE( fTrans[Ticker] ), SUMX( WINDOW( 1, ABS, -1, REL, Total_Cost_Tbl, ORDERBY( fTrans[Date], ASC ), , PARTITIONBY( fTrans[Ticker] ) ), [@Total_Cost] ) ) 4) Finally, the above 3 pieces of code come together via the following code: RETURN SUMX( fTrans, IF( fTrans[Transaction] = "Sale", Purch_Total_Sum, DIVIDE( Purch_Total_Sum + Total_Cost_Sum, [Saldo de cotas] ) ) ) But then I keep getting the circular dependency error message, which I tried to avoid by making sure the code picks up dependent data from PRIOR rows, albeit I fail on attempting to do so. How can I get this code fixed to achieve that? Or should a completely different calculation approach be taken? I suppose calculated columns could be a way to achieve that but I'd like to avoid that route as we're talking about a table with tens of thousands of rows of data. Thanks in advance for any help!
Circular dependency when using calculated column
Hello! This is my scenario. I have one table with % rates for different terms. My goal is to display a line graph that shows how the rate increases over time. So, if the rate in the first term was 1% and the rate in the second term was 2%, I want to see it it ploted as 1%, 3%. My approach so far was to create a calculated column(Result) based on a Rate and Value columns, using the value as an index to keep adding the previous Rate to the next one. It worked so far. My problem is that I manually entered the Rate values but want them to be dynamical, calculating them from another table where the data is. What I tried is another calculated column that does that calculation and now I have the ActualRate. When I tried to now substitute the new ActualRate for the original Rate in my Result column. I get a circular dependency error, so I got stuck there. I tried replacing the ActualRate calculated column as a measure, but the thing is that it does not add the previous values, it just displays the term. Here is my code for the Result calculated column: Result = VAR _currentRATE = Progression[Rate] VAR _currentValue = Progression[Value] VAR _sum = SUMX(FILTER('Progression', Progression[Value] <= _currentValue), Progression[Rate]) VAR _last0 = CALCULATE(MAX(Progression[Value]), FILTER(Progression, Progression[Value] < _currentValue && Progression[Value] = 0)) RETURN IF(_currentRATE = 0, 0, SUMX(FILTER(Progression, Progression[Value] >= _last0 && Progression[Value] <= _currentValue), Progression[Rate])) and my code for the ActualRate calculated column or measure: ActualRate = if( CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 = 0, 0,CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100) This is how my columns look like: And this is the current Result (with the measure) agains the expected Result (with the Rate calculated column): Any help is greatly appreciated! Thank you!Get most recent record from same column
Let's say I have a table that looks like this: Ticker Date Transaction Shares Price Average Cost ABEV3 01/Mar/2021 Purchase 77 $12.93 $12.93 ABEV3 04/May/2021 Purchase 73 $13.60 $13.26 ABEV3 04/Jun/2021 Sale 150 $17.91 $13.26 ABEV3 28/Jun/2021 Purchase 62 $16.01 $16.01 ABEV3 29/Oct/2021 Sale 62 $15.62 $16.01 ALSO3 07/Jan/2021 Purchase 39 $25.12 $25.12 ALSO3 03/Feb/2021 Sale 39 $27.20 $25.12 ALSO3 29/Jun/2021 Purchase 36 $27.53 $27.53 ALSO3 07/Jan/2022 Purchase 56 $17.85 $21.64 ALSO3 30/Mar/2022 Sale 92 $22.61 $21.64 Whenever there is a row with a PURCHASE the measure under the "Average Cost" column will recalculate a new average cost for that particular ticker. And whenever there is a row with a SALE all the measure needs to do is to pick up the latest calculated average cost for that same ticker from that same column. Every way I tried to do that I incur circular dependency error on my DAX code. How can I do that while avoiding circular dependency?
Avoiding Circular Discrepancies for Col A = Col B (last row) and Col B = Col A (current row)?
My goal: To use DAX to calculate Ending Inventory as a function of MAX(0, Beginning Inventory + Receipts - Forecast), for every day and every SKU. My Issue: I do not know how to lookup the previous day's Ending Inventory without PowerBI declaring a Circular Discrepancy. Clarification: Because Ending Inventory rounds up to 0 at the end of each day if the formula is negative, I cannot collapse my inventory calculation into a single column and subtract all forecast up to a given date and add all receipts up to a given date like this: (PseudoCode) Invalid Projected Inventory = Max( 0, LOOKUPVALUE(Current Inventory) + CALCULATE(SUM(Receipts.QTY), Receipts.Date <= Date, Receipts.Item = Item) - CALCULATE(SUM(Forecast.QTY), Forecast.Date <= Date, Forecast.Item = Item) ) The reason I cannot use that single Projected Inventory column is to consider the following example: Inventory for today (Oct 14) is 100 Total sum of Forecast thru Oct 20 is 200 Receipt of 100 will arrive Oct 21 Forecast on Oct 21, 2021 is 40 Desired Output: Projected Inventory on Oct 21 is 60: Inventory at start of Oct 21 is 0 (100 current inventory - 200 forecast, rounded up to 0) Receipt of 100 on Oct 21 - Forecast of 40 on Oct 21 = Projected Inventory of 60 Output of Invalid Formula: Projected Inventory = 0 Current Inventory of 100 - forecast thru Oct 21 of 240 + receipts of 100 = -40 Projected Inventory = Max(0, -40) = 0 Here is a screenshot of all the data I'm using in my test model (text data at the bottom), including my desired input which I can get working in Excel. And here is the same setup in PowerBI: But when I try to use DAX to calculate the Beginning and Ending inventory of each day, I get a circular discrepancy. Is there any way around this within DAX? Here are my formulas for the calculated table: Table Source: Daily Inventory = GENERATe(distinct('Date Table'[Date]), DISTINCT(Items[Item ID])) Beginning Inventory: Beginning Inventory = if('Daily Inventory'[Date]=today(), calculate(sum('Current Inventory'[Current Inventory]),filter('Current Inventory', 'Current Inventory'[Item ID]=[Item ID])), LOOKUPVALUE('Daily Inventory'[Ending Inventory],'Daily Inventory'[Item ID], [Item ID], 'Daily Inventory'[Date], [Date]-1) ) Receipts: Receipts = CALCULATE(sum(Receipts[QTY]), filter(Receipts, Receipts[Date]=earlier([Date])), filter(Receipts,Receipts[Item ID]=earlier([Item ID]))) Forecast: Forecast = CALCULATE(sum(Forecast[QTY]), filter(Forecast, Forecast[Date]=earlier([Date])), filter(Forecast,Forecast[Item ID]=earlier([Item ID]))) Ending Inventory: Ending Inventory = max([Beginning Inventory] + [Receipts] - [Forecast],0) And here are my data tables: Date Table Date 10/14/2021 10/15/2021 10/16/2021 10/17/2021 10/18/2021 Items Item ID Item Name 1 TestSKU1 2 TestSKU2 Current Inventory Item ID Current Inventory 1 100 2 100 Forecast Item ID Date QTY 1 10/14/2021 14 1 10/15/2021 12 1 10/16/2021 3 1 10/17/2021 24 1 10/18/2021 16 2 10/14/2021 43 2 10/15/2021 46 2 10/16/2021 7 2 10/17/2021 22 2 10/18/2021 32 Receipts Item ID Date QTY 1 10/17/2021 200 2 10/17/2021 200
