How to avoid circular dependency when I the value of the column is its previous value plus other val

[wellidyalmeida]

Hi all.

I'm facing with a circular dependency error and all I tried not solved.
I have 3 columns:
Column A
Column B
Column C

The value of the column C is
(A + [previous value of C]) - B

Some trick to achieve this?

This image show how I did this in excel

Thanks in advance

[tamerj1 · Accepted solution]

Hi @wellidyalmeida 
This is called recursive calculation which is not supported by Power Bi as the DAX engine evaluates the calculated column or the measure at once. 
However, there is a workaround and in this specific case it is not complicated. For this to work properly you need to specify the following: 
1. The starting value for Column C

2. What you state as Column A, Column B & Column C, are they columns or measures?
Attached is a sample file tailored to match your requirement for both calculated column and measure options

C = 
VAR StartingValue = 2000
VAR MinDate = MIN ( Table1[Date] )
VAR CurrentDate = Table1[Date] 
VAR FilteredTable = FILTER ( Table1, Table1[Date] > MinDate && Table1[Date] <= CurrentDate )
VAR RecursiveValue = SUMX ( FilteredTable, [A] - [B] )
RETURN
    StartingValue + RecursiveValue

Measure C = 
VAR StartingValue = 2000
VAR SelectedDates = ALLSELECTED ( Table2[Date] ) 
VAR MinDate = MINX ( SelectedDates, Table2[Date] )
VAR CurrentDate = MAX ( Table2[Date] )
VAR FilteredTable = FILTER ( SelectedDates, Table2[Date] > MinDate && Table2[Date] <= CurrentDate )
VAR MeasuresTable = ADDCOLUMNS ( FilteredTable, "@A", [Measure A], "@B", [Measure B] )
VAR RecursiveValue = SUMX ( MeasuresTable, [@A] - [@B] )
RETURN
    StartingValue + RecursiveValue

[wellidyalmeida]

Hi, @tamerj1 , I need your help 🙂

The value of Column C is not correct.

I've been debuged every value used in the calculation, unfortunelly, I coundn´t understand what is wrong.

I used the COLUMN SOLUTION.

Can you help me, please?

I attached pbix and explaning it:

https://drive.google.com/file/d/1eX9hO1uNTAd1gaV3i57e-mvZ1kiqllfk/view?usp=sharing

Starting value is 359606 and come from the 'saldo inicial' table.

The calculated column is in the 'contas_vw' table in the column 'C'

The expected value is 181.737,87 but I got 182.367,83 as you can see in this image

[wellidyalmeida · Accepted solution]

Solved!

The original solution from @tamerj1 is correct.

The sequence that calculated columns are evaluated caused the problem. My column C, beside the recursive calculation, has conditionals that gave me headache.

[wellidyalmeida]

Thank you, @tamerj1. This solved the problem.

[tamerj1 · Accepted solution]

Hi @wellidyalmeida 
This is called recursive calculation which is not supported by Power Bi as the DAX engine evaluates the calculated column or the measure at once. 
However, there is a workaround and in this specific case it is not complicated. For this to work properly you need to specify the following: 
1. The starting value for Column C

2. What you state as Column A, Column B & Column C, are they columns or measures?
Attached is a sample file tailored to match your requirement for both calculated column and measure options

C = 
VAR StartingValue = 2000
VAR MinDate = MIN ( Table1[Date] )
VAR CurrentDate = Table1[Date] 
VAR FilteredTable = FILTER ( Table1, Table1[Date] > MinDate && Table1[Date] <= CurrentDate )
VAR RecursiveValue = SUMX ( FilteredTable, [A] - [B] )
RETURN
    StartingValue + RecursiveValue

Measure C = 
VAR StartingValue = 2000
VAR SelectedDates = ALLSELECTED ( Table2[Date] ) 
VAR MinDate = MINX ( SelectedDates, Table2[Date] )
VAR CurrentDate = MAX ( Table2[Date] )
VAR FilteredTable = FILTER ( SelectedDates, Table2[Date] > MinDate && Table2[Date] <= CurrentDate )
VAR MeasuresTable = ADDCOLUMNS ( FilteredTable, "@A", [Measure A], "@B", [Measure B] )
VAR RecursiveValue = SUMX ( MeasuresTable, [@A] - [@B] )
RETURN
    StartingValue + RecursiveValue