Aggregate by date, unsolved circular reference error

Question

Hi,&nbsp;so for work I need to set up future outstanding notionals of a mortgage portfolio. The amount is split up in sums and it is listed when prepayment is expected. See a symplified example below. For simplicity, it is a small portfolio that will be repaid in the next few months. For completeness I will add that I have a different table from which the data is sourced. The source is a table with a manyfold of below (data below where the portfolio is split in its components).&nbsp;Date is set up by listing all unique dates from the other table.&nbsp;Cash flow is created by aggregating all cash flows (of all portfolio components) by date into this table.&nbsp;DateCash flow31-07-202230,00031-08-202225,00030-09-202215,00031-10-202230,00030-11-202225,00031-12-202245,00031-01-202220,000&nbsp;What I want to do is to add a column that shows what the outstanding notional is, which is a sum of the cash flow in the current date and the future ones. In excel I would formulate this as B2 = SUM(A2 : $A$7), so B3 = SUM(A3 : $A$7). This should look as below:&nbsp;DateCash flowOutstanding notional31-07-202230,000190,00031-08-202225,000160,00030-09-202215,000135,00031-10-202230,000120,00030-11-202225,00090,00031-12-202245,00065,00031-01-202220,00020,000&nbsp;Now, I have tried a few options with help of the forum. Such as below.&nbsp;Outstanding notional = CALCULATE (SUM (Summary[Cash flow]),FILTER (ALL (Summary ),'Summary'[Date] &lt;= MAX ( 'Summary'[Date])))Outstanding notional = CALCULATE(SUM(Summary[Cash flow]), ALL(Summary), Summary[Date] &lt;= EARLIER(Summary[Date]))&nbsp;It seems to work for others, but I get the following error message, indicating a circular reference. pi&gt;A circular dependency was detected: Summary[Notional normal BTL], Summary[Column], Summary[Notional normal BTL].&lt;/pi&gt;&nbsp;Can you please tell me what I am doing wrong? It would seem a rather simple execution (at least it is in excel) but I can't seem to figure this one out. Thanks in advance, much appreciated.

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
You can create a calculated column or measure as below to get [Outstanding notional], please find the details in the attachment...
Calculated column:
Outstanding notional = 
CALCULATE (
    SUM ( 'Summary'[Cash flow] ),
    FILTER ( 'Summary', 'Summary'[Date] &gt;= EARLIER ( 'Summary'[Date] ) )
)

Measure:
Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Summary'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Summary'[Cash flow] ),
        FILTER ( ALLSELECTED ( 'Summary' ), 'Summary'[Date] &gt;= _seldate )
    )
Running (Cumulative) Totals in Power BI
Cumulative Total/ Running Total in Power BI
In addition, you can refer the following links to understand the circular dependencies and how to avoid circular dependencies...
Understanding circular dependencies in DAX
Avoiding circular dependency errors in DAX
Best Regards

Forum Discussion

Aggregate by date, unsolved circular reference error

2 Replies

Related content

Unsolveable Mashup Data Access Error

Data Aggregation issue

dynamic reference line

Azure Key Vault References

referer (referring) URL in usage stats?

Date	Cash flow
31-07-2022	30,000
31-08-2022	25,000
30-09-2022	15,000
31-10-2022	30,000
30-11-2022	25,000
31-12-2022	45,000
31-01-2022	20,000

Date	Cash flow	Outstanding notional
31-07-2022	30,000	190,000
31-08-2022	25,000	160,000
30-09-2022	15,000	135,000
31-10-2022	30,000	120,000
30-11-2022	25,000	90,000
31-12-2022	45,000	65,000
31-01-2022	20,000	20,000