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

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!