Aggregate by date, unsolved circular reference error

[Anonymous]

Hi,

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

Date is set up by listing all unique dates from the other table.

Cash flow is created by aggregating all cash flows (of all portfolio components) by date into this table.

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

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:

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

Now, I have tried a few options with help of the forum. Such as below.

Outstanding notional = CALCULATE (SUM (Summary[Cash flow]),FILTER (ALL (Summary ),'Summary'[Date] <= MAX ( 'Summary'[Date])))

Outstanding notional = CALCULATE(SUM(Summary[Cash flow]), ALL(Summary), Summary[Date] <= EARLIER(Summary[Date]))

It seems to work for others, but I get the following error message, indicating a circular reference.

pi>A circular dependency was detected: Summary[Notional normal BTL], Summary[Column], Summary[Notional normal BTL].</pi>

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

Hi @Anonymous ,

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] >= EARLIER ( 'Summary'[Date] ) )
)

Measure:

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Summary'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Summary'[Cash flow] ),
        FILTER ( ALLSELECTED ( 'Summary' ), 'Summary'[Date] >= _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

[Anonymous]

Thanks! This seems to work. Also big thanks for uploading a Power BI file and links to circular references in Power BI!