Circular Dependency

[Gabriel_Pedri]

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 calculate the current one, and this is causing an error.

We have two measures, Initial Cash Balance and Final Cash Balance. The Initial for January is given for the base, and the Final is the sum of 5 accounts, including Initial. For the next month, we have Initial for February, which is equal to the last month's Final, and the Final again is the sum of 5 accounts, including Initial.

Basically, my issue revolves around Initial Cash Balance values that don't exist in the table; we need to calculate them. However, Final Cash Balance also requires the Initial to be calculated. How can I resolve this?

It's noticeable that one depends on the other to be calculated. However, we can establish

a sequence of steps that, if ordered, would allow us to calculate up to the month of December without any issues.

Here is the code for both measures:

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

Initial Cash Balance =

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

VAR varFinal =

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

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

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

Final Cash Balance =
VAR varFinal =

CALCULATE(
[Currency Amount],
dimCashFlowAccount[H1] = "1 Operating " ||
dimCashFlowAccount[H1] = "2 Financing" ||
dimCashFlowAccount[H1] = "3 Investment"||
dimCashFlowAccount[H1] = "4 Cash Exchange Variation"
)

RETURN varFinal + [Initial Cash Balance]

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

Currently, my matrix looks like this:

H1	Jan	Fev	Mar	Abr
1 Operating	-45.069.886,14	-38.754.603,82	-746.247,73	10.903.513,73
2 Financing	-1.339.127,96	-705.556,47	-25.845.105,76	-225.528,30
3 Investment	-33.875.353,61	-10.865.816,03	-9.160.827,52	-2.430.627,32
4 Cash Exchange Variation	-2.077.440,02	1.349.943,37	-1.081.836,05	47.619,05
5 Initial Cash Balance	773.156.021,79	690.794.214,06	641.818.181,11
6 Final Cash Balance	690.794.214,06	641.818.181,11	604.984.164,05	8.294.977,16

However, the value I'm aiming to achieve is this:

H1	Jan	Fev	Mar	Abr
1 Operating	-45.069.886,14	-38.754.603,82	-746.247,73	10.903.513,73
2 Financing	-1.339.127,96	-705.556,47	-25.845.105,76	-225.528,30
3 Investment	-33.875.353,61	-10.865.816,03	-9.160.827,52	-2.430.627,32
4 Cash Exchange Variation	-2.077.440,02	1.349.943,37	-1.081.836,05	47.619,05
5 Initial Cash Balance	773.156.021,79	690.794.214,06	641.818.181,11	604.984.164,05
6 Final Cash Balance	690.794.214,06	641.818.181,11	604.984.164,05	613.279.141,21

Could you help me?

[Gabriel_Pedri · Accepted solution]

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]

I understand that there was no dynamism in the code, which could have been easily calculated using YTD or BETWEEN. However, in this case, there is a circular dependency in the measures, so I had to opt for a more static method, trying to calculate month by month, making it possible to calculate the current month.

In my case, we would have the Initial Cash Balance value only for the month of January. Because of this, I pulled it at the beginning of the code to be able to calculate the others!

[v-kongfanf-msft]

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]

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]

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

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/1447...

[Gabriel_Pedri]

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?

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached files.  Please study these files and adapt the formulas therein to your specific case.

Hope this helps.

[Gabriel_Pedri]

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]

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. 

[lbendlin]

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

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/1447...

[Gabriel_Pedri]

I found information regarding file sharing on the forum, and it's only possible with permission.

https://community.fabric.microsoft.com/t5/Desktop/The-file-type-pbix-is-not-supported/m-p/3308037

Could you provide an email so I can share the file?

[Gabriel_Pedri]

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!