Forum Discussion

Gabriel_Pedri's avatar
Gabriel_Pedri
Resolver I
2 years ago
Solved

Circular Dependency

I'm having a dependency issue with my DAX measures; essentially, one measure needs the other to be calculated. However, it's not related to time; I need the previous month's value of one measure to c...
Need Help
  • Gabriel_Pedri's avatar
    Gabriel_Pedri
    2 years ago

    The solution I found just below:

    Initial Cash Balance =

    VAR varNum =
    SWITCH (
    MONTH(MAX('dimCalendar'[Date])),
    1, 0,
    2, -1,
    3, -2,
    4, -3,
    5, -4,
    6, -5,
    7, -6,
    8, -7,
    9, -8,
    10, -9,
    11, -10,
    12, -11
    )

    VAR varMonth = MONTH(MAX('dimCalendar'[Date]))

    VAR varInitial =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "5 Initial Cash Balance"
    )

    // FEBRUARY
    VAR X =
    CALCULATE(
    [DefaultCase],
    DATEADD(dimCalendar[Date], varNum, MONTH)
    )

    VAR Y =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 1, MONTH)
    ) + X

    // MARCH
    VAR X1 = Y

    VAR Y1 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 2, MONTH)
    ) + X1

    // APRIL
    VAR X2 = Y1

    VAR Y2 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 3, MONTH)
    ) + X2

    // MAY
    VAR X3 = Y2

    VAR Y3 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 4, MONTH)
    ) + X3

    // JUNE
    VAR X4 = Y3

    VAR Y4 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 5, MONTH)
    ) + X4

    // JULY
    VAR X5 = Y4

    VAR Y5 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 6, MONTH)
    ) + X5

    // AUGUST
    VAR X6 = Y5

    VAR Y6 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 7, MONTH)
    ) + X6

    // SEPTEMBER
    VAR X7 = Y6

    VAR Y7 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 8, MONTH)
    ) + X7

    // OCTOBER
    VAR X8 = Y7

    VAR Y8 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 9, MONTH)
    ) + X8

    // NOVEMBER
    VAR X9 = Y8

    VAR Y9 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 10, MONTH)
    ) + X9

    // DECEMBER
    VAR X10 = Y9

    VAR Y10 =
    CALCULATE(
    [Currency Amount],
    (dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation") &&
    DATEADD(dimCalendar[Date], varNum + 11, MONTH)
    ) + X10

    VAR Result =
    SWITCH(
    varMonth,
    2, X, // If CurrentMonth is 2 (February), return X
    3, X1, // If CurrentMonth is 3 (March), return X1
    4, X2, // If CurrentMonth is 4 (April), return X2
    5, X3, // If CurrentMonth is 5 (May), return X3
    6, X4, // If CurrentMonth is 6 (June), return X4
    7, X5, // If CurrentMonth is 7 (July), return X5
    8, X6, // If CurrentMonth is 8 (August), return X6
    9, X7, // If CurrentMonth is 9 (September), return X7
    10, X8, // If CurrentMonth is 10 (October), return X8
    11, X9, // If CurrentMonth is 11 (November), return X9
    12, X10, // If CurrentMonth is 12 (December), return X10
    BLANK() // Default case
    )

    RETURN
    IF(varInitial <> 0, varInitial, Result)


    ========================================================

    Final Cash Balance =

    VAR varNum =
    SWITCH (
    MONTH(MAX('dimCalendar'[Date])),
    1, 0,
    2, -1,
    3, -2,
    4, -3,
    5, -4,
    6, -5,
    7, -6,
    8, -7,
    9, -8,
    10, -9,
    11, -10,
    12, -11
    )

    VAR varMonth = MONTH(MAX('dimCalendar'[Date]))

    VAR varInitial =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "5 Initial Cash Balance"
    )

    VAR varFinal =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "6 Final Cash Balance"
    )

    // FEBRUARY

    VAR X =
    CALCULATE(
    [DefaultCase],
    DATEADD(dimCalendar[Date], varNum, MONTH)
    )

    VAR Y =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +1, MONTH)
    ) + X

    // MARCH

    VAR X1 = Y

    VAR Y1 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +2, MONTH)
    ) + X1

    // APRIL

    VAR X2 = Y1

    VAR Y2 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +3, MONTH)
    ) + X2

    // MAY
    VAR X3 = Y2

    VAR Y3 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +4, MONTH)
    ) + X3

    // JUNE
    VAR X4 = Y3

    VAR Y4 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +5, MONTH)
    ) + X4

    // JULY
    VAR X5 = Y4

    VAR Y5 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +6, MONTH)
    ) + X5

    // AUGUST
    VAR X6 = Y5

    VAR Y6 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +7, MONTH)
    ) + X6


    // SEPTEMBER
    VAR X7 = Y6

    VAR Y7 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +8, MONTH)
    ) + X7

    // OCTOBER
    VAR X8 = Y7

    VAR Y8 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +9, MONTH)
    ) + X8

    // NOVEMBER
    VAR X9 = Y8

    VAR Y9 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +10, MONTH)
    ) + X9

    // DECEMBER
    VAR X10 = Y9

    VAR Y10 =
    CALCULATE(
    [Currency Amount],
    dimCashFlowAccount[H1] = "1 OCF - Operating [IFRS]" ||
    dimCashFlowAccount[H1] = "2 FCF - Financing" ||
    dimCashFlowAccount[H1] = "3 ICF - Investment" ||
    dimCashFlowAccount[H1] = "4 Cash Exchange Variation",
    DATEADD(dimCalendar[Date], varNum +11, MONTH)
    ) + X10

    VAR Result =
    SWITCH(
    varMonth,
    2, Y, // If CurrentMonth is 2 (February), return X
    3, Y1, // If CurrentMonth is 3 (March), return X1
    4, Y2, // If CurrentMonth is 4 (April), return X2
    5, Y3, // If CurrentMonth is 5 (May), return X3
    6, Y4, // If CurrentMonth is 6 (June), return X4
    7, Y5, // If CurrentMonth is 7 (July), return X5
    8, Y6, // If CurrentMonth is 8 (August), return X6
    9, Y7, // If CurrentMonth is 9 (September), return X7
    10, Y8, // If CurrentMonth is 10 (October), return X8
    11, Y9, // If CurrentMonth is 11 (November), return X9
    12, Y10, // If CurrentMonth is 12 (December), return X10
    BLANK() // Default case
    )

    RETURN
    IF(varInitial <> 0, varFinal, Result)


    If anyone finds a simplification of the code, I would greatly appreciate improvements and tips. Thank you very much for the community support !!!

  • Gabriel_Pedri's avatar
    Gabriel_Pedri
    Resolver I
    2 years ago

    Ashish_Mathur, thank you very much for the response!!

     

    I spent some time looking at your files and noticed that you use a method for date ranges to calculate values using BETWEEN, in addition to using MIN and ALLEXCEPT to prevent possible errors that CALCULATE may generate.

     

    Honestly, I couldn't fully understand how these formulas can help me avoid circular dependencies.

    Could you give me a brief explanation of what your problem was and how you managed to solve it?

     

    If possible, could you provide the before and after files?

    I appreciate your attention in advance.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      You are welcome.  Your simple taks is to calculate the closing balance and that is exactly the problem that i have solved in those 2 files.