Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Aggregate by date, unsolved circular reference error

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....
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

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