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 !!!

lbendlin's avatar
lbendlin
Super User
2 years ago

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Gabriel_Pedri's avatar
    Gabriel_Pedri
    Resolver I
    2 years ago

    Lbendlin, I appreciate your attention in advance.

     

    I'm having trouble attaching the .pbix file!!

     

    I created a model specifically for your understanding; however, it returns the message "The file type (.pbix) is not supported," even though the file is only 89 KB.

     

    I will leave some images of my model and what I hope to achieve!!

     

     

    Initial Cash Balance = Final Cash Balance Last Month

    Final Cash Balance = (account) 1 + 2 + 3 + 4 + 5

     

    If you have any questions, I am at your disposal!