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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Gabriel_Pedri ,

 

Based on the information you have provided, the circular dependencies can be resolved by using SUMMARIZE to create a table containing the required values for each date and then using COALESCE to handle the circular dependencies. Refer to the modified code below:

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

VAR varFinal =
    CALCULATE(
        [Final Cash Balance],
        DATEADD(dimCalendar[Date], -1, MONTH)
    )

RETURN
    IF(
        HASONEVALUE(dimCalendar[Date]),
        COALESCE(
            SELECTCOLUMNS(InitialTable, [varInitial]),
            varFinal
        )
    )

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Gabriel_Pedri's avatar
Gabriel_Pedri
Resolver I
2 years ago

I'm sorry, but the circular dependency error still occurs.

An interesting approach would be to create a virtual table; however, it would be useful for dependencies that arise due to columns rather than measures.

I would like to share my adapted file to help you understand the scenario, but without permission, I don't see how to provide it.

Thank you in advance for your help.

  • 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

      I accessed the link you provided me, highlighted the passage regarding authorization to share files

      1) Uploading files

      Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.

      I will send you my file that contains the content via Google Drive, okay?