Forum Discussion

marinahani's avatar
marinahani
New Member
1 year ago
Solved

Circular Dependency - 2 columns referencing each other

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 c...
Need Help
  • marinahani's avatar
    marinahani
    1 year ago

    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])
        )
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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's avatar
marinahani
New Member
1 year ago

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's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      marinahani
      New Member
      1 year ago

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