Forum Discussion

Robin96's avatar
Robin96
Icon for Helper II rankHelper II
1 year ago
Solved

Circular dependency in DAX

Hello all,   i have been trying to make this calculation work for some days now, but i cant seem to get past the circular dependency in my dax measure, because the two measures are refering to each...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hi Robin96 ,

     

    Thank you for reaching out to the Microsoft Fabric Community. I implemented your scenario, and I was able to achieve the expected results as per your requirements. Please review the output.
    Result:

    Used Measure:

    Cash_T = 
VAR CurrentMonth = MAX('Table'[Date]) 
VAR PrevMonthCash = 
    CALCULATE(
        MAX('Table'[Cash (t)]), 
        FILTER(
            ALL('Table'),
            'Table'[Date] = EOMONTH(CurrentMonth, -1)  // Get last month's Cash_T1
        )
    ) 
RETURN
IF(
    MONTH(CurrentMonth) = 1, 
    MAX('Table'[Cash (t)]),  // Keep the initial cash balance for January
    PrevMonthCash
)

     

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

You need to perform each calculation separately, and each needs to accumulate all the prior entries. So [Cash (t)] would accumulate all entries prior to the given date, and [Cash (t+1)] would accumulate all values prior to and including the given date.

Something like

Cash (t) =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Change in cash] ) + SUM ( 'Table'[Cash] ),
        'Date'[Date] < MinDate
    )
RETURN
    Result

Cash (t+1) =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Change in cash] ) + SUM ( 'Table'[Cash] ),
        'Date'[Date] <= MaxDate
    )
RETURN
    Result