circular reference error

[georgy]

Hi All,

I have problem where I need to use prior period closing balance as current period opening balance. Something like table below. The problem is that ADDITION column is % of opening balance and there many more other things (% and absolute values which contribute to closing balance) in real data so I can't use formulas. I literally need to use D2 for B3 value in Power BI. Is it possible?

Thank you.

[MFelix]

Hi @georgy ,

Can you share some more information about the addition column especcially on how it returns the value based on the Opening Balance?

If the addition values was a column value you just needed to create the following two measures:

Opening Balance =
1
    + CALCULATE (
        SUM ( 'Addition Table'[Addition] );
        FILTER (
            ALL ( 'Addition Table'[Period] );
            'Addition Table'[Period] < MIN ( 'Addition Table'[Period] )
        )
    )

Closing period = [Opening Balance] + SUM('Addition Table'[Addition])

However this will only work if your column addition is based values you just need to define the opening balance for the first period. Being different calculation based on the percentages and other thins this need to be adjusted.

Check PBIX file attach.

Regards,

MFelix

[georgy]

Hi MFelix,

Thank you for a quick reply.

As mentioned additions are % of opening balance, there are losses % as well and other absolute values that change from month to month. Something like table below.

Cheers,