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

 

H1JanFevMarAbr
1 Operating-45.069.886,14-38.754.603,82-746.247,7310.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,021.349.943,37-1.081.836,0547.619,05
5 Initial Cash Balance773.156.021,79690.794.214,06641.818.181,11 
6 Final Cash Balance690.794.214,06641.818.181,11604.984.164,05

8.294.977,16


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

 

H1JanFevMarAbr
1 Operating-45.069.886,14-38.754.603,82-746.247,7310.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,021.349.943,37-1.081.836,0547.619,05
5 Initial Cash Balance773.156.021,79690.794.214,06641.818.181,11604.984.164,05
6 Final Cash Balance690.794.214,06641.818.181,11604.984.164,05613.279.141,21

 

Could you help me?

 

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

12 Replies

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

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

  • 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

    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

      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!