Circular dependency in DAX

[Robin96]

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 eachother. 

This is what i am trying to achieve.

This is my calculation for Cash (t). So for january i have an accumulated value. and the rest of months i am showing the last months value from cash (t+1).

Cash (t) = 
    VAR period = SELECTEDVALUE('DATE'[Month no])

    var CumulativeTotal =
    CALCULATE(
        [Balance SUM],TREATAS(VALUES('Rep Tree'[Head.acc]),Fact_table[dim1]),'Rep Tree'[Group] = "Cash and Cash Equivalent",
        FILTER(
            ALL('date'),
            'DATE'[Year-Month] <= MAX('DATE'[Year-Month])
        )
    )

    VAR cash_t =
        CALCULATE(
            [Cash (t+1)], 
            PREVIOUSMONTH('DATE'[Date])
        )

    VAR result = 
        IF(
            period = 1, CumulativeTotal, cash_T
            )

        RETURN result

The issue occurs when i try to use this measure to calculate Cash (t+1). i have my language in Norwegian, but its saying that an circular dependency was located. 

Any tips would be highly appreciated! thanks.

[V-yubandi-msft]

Hi @Robin96 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[V-yubandi-msft]

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft]

Hi @Robin96 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

[V-yubandi-msft · Accepted solution]

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]

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