Circular Dependency - 2 columns referencing each other

[marinahani]

Hi - would appreciate some help on this.

This is what I have right now. But the correct calculation for TotalCurrentMonth should also include value from FromPreviousMonth. Which then causes the circular dependency issue. 

TotalCurrentMonth =

    SUMX(

        'MRR Level 2',  

        'MRR Level 2'[FromPreviousMonth] +

        'MRR Level 2'[New Logo] +

        'MRR Level 2'[Cross Sell] +

        'MRR Level 2'[True Up] +

        'MRR Level 2'[Price Increase] +

        'MRR Level 2'[True Down] +

        'MRR Level 2'[Price Decrease] +

        'MRR Level 2'[Churn]

    )

FromPreviousMonth =

    CALCULATE(

        SUMX(

            'MRR Level 2',

            'MRR Level 2'[TotalCurrentMonth]

        ),

        DATEADD('MRR Level 2'[FirstofMonthMRRDate], -1, MONTH)

    )

The calculated result (excluding the FromPreviousMonth value)

The expected result:

[v-karpurapud]

Hi @marinahani 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[marinahani]

Thanks and done. 

[v-karpurapud]

Hello @marinahani 

Thanks for reaching out to the Microsoft Fabric Community Forum

We just wanted to confirm that the solution provided by @danextian  is accurate and should help resolve the issue you're facing.Could you please confirm if your query have been resolved or not?If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently. If you continue to face issues, feel free to reach out to us for further assistance!

Thank you!

[danextian]

Hi @marinahani 

Assuming that is really how your data looks like, you don't really need to reference from previous month in your  current month total.

Current Month Measure =
SUMX (
    'Table',
    'Table'[Churn] + 'Table'[New Logo] + 'Table'[Price Decrea] + 'Table'[Price Incr] + 'Table'[True Up] + 'Table'[True Dow]
)

And you can use either of these measures to get the previous month

Previous Month = 
CALCULATE (
    [Current Month],
    FILTER (
        ALL ( 'Table'[FirstofMonthMRRDate] ),
        'Table'[FirstofMonthMRRDate]
            = EDATE ( MAX ( 'Table'[FirstofMonthMRRDate] ), -1 )
    )
)

Previous Month2 = 
CALCULATE (
    [Current Month],
    PREVIOUSMONTH ('Table'[FirstofMonthMRRDate] )
)

[marinahani]

My expected result should be this below. You can see how the TotalCurrentMonth is an incremental value over time. It's adding up all this month's values and the incremental value from previous month

[danextian]

Hi @marinahani 

You're trying to sum the cumulative value. Try this:

Sum of Cumulative = 
VAR CurrentDate = MAX('Table'[FirstofMonthMRRDate])

RETURN
CALCULATE(
    SUMX(
        SUMMARIZECOLUMNS(
            'Table'[FirstofMonthMRRDate],
            "@cumulative", [Previous Month2]
        ),
        [@cumulative]
    ),
    'Table'[FirstofMonthMRRDate] <= CurrentDate
)

[marinahani · Accepted solution]

I took your suggestion and did some tweaks and it worked. Managed to get the result exactly as needed

CALCULATE(

    SUMX(

        'MRR Level 2',

        'MRR Level 2'[New Logo] +

        'MRR Level 2'[Cross Sell] +

        'MRR Level 2'[True Up] +

        'MRR Level 2'[Price Increase] +

        'MRR Level 2'[True Down] +

        'MRR Level 2'[Price Decrease] +

        'MRR Level 2'[Churn]

    ) ,

    FILTER(

        ALL('MRR Level 2'[FirstofMonthMRRDate]),

        'MRR Level 2'[FirstofMonthMRRDate] <= MAX('MRR Level 2'[FirstofMonthMRRDate])

    )

[FarhanJeelani]

Hi @marinahani ,

You're encountering a circular dependency issue because TotalCurrentMonth depends on FromPreviousMonth, and FromPreviousMonth depends on TotalCurrentMonth. To resolve this, you need to break the dependency by using variables or iterators.

Try this approach:

DAX

VAR PrevMonthValue =
CALCULATE(
MAX('MRR Level 2'[TotalCurrentMonth]),
DATEADD('MRR Level 2'[FirstofMonthMRRDate], -1, MONTH)
)

RETURN
SUMX(
'MRR Level 2',
PrevMonthValue +
'MRR Level 2'[New Logo] +
'MRR Level 2'[Cross Sell] +
'MRR Level 2'[True Up] +
'MRR Level 2'[Price Increase] +
'MRR Level 2'[True Down] +
'MRR Level 2'[Price Decrease] +
'MRR Level 2'[Churn]
)

• The PrevMonthValue variable fetches the previous month's TotalCurrentMonth without creating a direct dependency.
• The calculation then adds up the values without referencing FromPreviousMonth in a way that causes a loop.

If you still face issues, consider storing FromPreviousMonth in a separate calculated table and referencing it instead.

Please mark this post as solution if it helps you. Appreciate Kudos.

[marinahani]

I tried but it didnt work, so I started to create a separate calculated table for PreviousMonth, but then I tried the Cumulative Sum approach suggested and it worked