Forum Discussion

MBZA's avatar
MBZA
Icon for Helper I rankHelper I
10 months ago
Solved

Strange CLOSINGBALANCE behaviour

TLDR: I am trying to get a closing balance for a measure using the CLOSINGBALANCEMONTH function. For some reason for the end-of-quarter values it is summing the monthly values instead of giving the end-of-quarter value. But if I change it slightly in tests I get the right answer.

A simplified version of the measure is as follows:

BTD with MTD Amount test = 
VAR res = CALCULATE(
    SUMX('Financial Data', 
        VAR amt = 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper]
        RETURN 
            amt 
            ))
RETURN 
    CLOSINGBALANCEMONTH(res, 'Fiscal Period'[Fiscal Month])

My calendar table (Fiscal Period) and fact table (Financial Data) are at a monthly granularity with Fiscal Month as the relationship column.

Using the above measure if I look by Quarter and Fiscal month I am getting a table like this, which is obviously not expected (I'd expect the quarterly figures to be 100)

Fiscal QuarterBTD with MTD Amount Test
2025-Q1300
2025-Q2300
2023-Q3300
    2025/07/31100
    2025/08/31100
    2025/09/30100
2025-Q4100
    2025/10/31100

 

If I replace the measure with the following however, I get the correct answer:

BTD with MTD Amount test = CLOSINGBALANCEMONTH(CALCULATE(SUMX('Financial Data', 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper])), 'Fiscal Period'[Fiscal Month])

To me these two versions are logically equivalent, so why are they giving different answers?

In practice my SUMX has a whole bunch more logic inside it which is why I'm separating it out into a separate VAR to keep the code manageable.

But I am also getting the correct answer in the real one if I move everything out of the VAR into the CLOSINGBALANCEMONTH.

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    10 months ago

    Hi MBZA,

    Thats weird behavior but it seems like in your first version

    BTD with MTD Amount test = 
VAR res = CALCULATE(
    SUMX('Financial Data', 
        VAR amt = 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper]
        RETURN 
            amt 
            ))
RETURN 
    CLOSINGBALANCEMONTH(res, 'Fiscal Period'[Fiscal Month])

    that the variable res is evaluated first in the current filter context (which is at quarter level includes all months in quarter) then CLOSINGBALANCEMONTH operates on that already aggregated value

     

    And in your second version (working solution you provided)

    BTD with MTD Amount test = CLOSINGBALANCEMONTH(CALCULATE(SUMX('Financial Data', 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper])), 'Fiscal Period'[Fiscal Month])

    that he entire expression is evaluated inside the CLOSINGBALANCEMONTH function (so it can properly apply the end of month context)

    So here is some Approach you can try (I hope it works):

    • Use a measure reference
    BTD with MTD Amount test = 
VAR BaseMeasure = [Your Base Measure]  // Reference another measure
RETURN 
    CLOSINGBALANCEMONTH(BaseMeasure, 'Fiscal Period'[Fiscal Month])
    •  Put the all calculation in CALCULATE 
    BTD with MTD Amount test = 
VAR complexCalculation = 
    SUMX('Financial Data', 
        // Your complex logic here
    )
RETURN 
    CLOSINGBALANCEMONTH(CALCULATE(complexCalculation), 'Fiscal Period'[Fiscal Month])

    • Use iterator functions

       

    BTD with MTD Amount test = 
SUMX(
    VALUES('Fiscal Period'[Fiscal Month]),
    CLOSINGBALANCEMONTH(
        CALCULATE(
            SUMX('Financial Data', 
                // Your complex logic
            )
        ),
        'Fiscal Period'[Fiscal Month]
    )
)


    So the idea here is that time intelligence functions need to control the filter context themselves (they cant work properly on pre-calculated results)

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

5 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Icon for Super User rankSuper User
    10 months ago

    Hi MBZA,

    Thats weird behavior but it seems like in your first version

    BTD with MTD Amount test = 
VAR res = CALCULATE(
    SUMX('Financial Data', 
        VAR amt = 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper]
        RETURN 
            amt 
            ))
RETURN 
    CLOSINGBALANCEMONTH(res, 'Fiscal Period'[Fiscal Month])

    that the variable res is evaluated first in the current filter context (which is at quarter level includes all months in quarter) then CLOSINGBALANCEMONTH operates on that already aggregated value

     

    And in your second version (working solution you provided)

    BTD with MTD Amount test = CLOSINGBALANCEMONTH(CALCULATE(SUMX('Financial Data', 'Financial Data'[BTD with MTD Amount lower] + 1000 * 'Financial Data'[BTD with MTD Amount upper])), 'Fiscal Period'[Fiscal Month])

    that he entire expression is evaluated inside the CLOSINGBALANCEMONTH function (so it can properly apply the end of month context)

    So here is some Approach you can try (I hope it works):

    • Use a measure reference
    BTD with MTD Amount test = 
VAR BaseMeasure = [Your Base Measure]  // Reference another measure
RETURN 
    CLOSINGBALANCEMONTH(BaseMeasure, 'Fiscal Period'[Fiscal Month])
    •  Put the all calculation in CALCULATE 
    BTD with MTD Amount test = 
VAR complexCalculation = 
    SUMX('Financial Data', 
        // Your complex logic here
    )
RETURN 
    CLOSINGBALANCEMONTH(CALCULATE(complexCalculation), 'Fiscal Period'[Fiscal Month])

    • Use iterator functions

       

    BTD with MTD Amount test = 
SUMX(
    VALUES('Fiscal Period'[Fiscal Month]),
    CLOSINGBALANCEMONTH(
        CALCULATE(
            SUMX('Financial Data', 
                // Your complex logic
            )
        ),
        'Fiscal Period'[Fiscal Month]
    )
)


    So the idea here is that time intelligence functions need to control the filter context themselves (they cant work properly on pre-calculated results)

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    10 months ago

    Hi MBZA 

    Your two DAX versions behave differently because the VAR forces the SUMX to evaluate in the current filter context.
    So at quarter level, the VAR already holds the sum of all months, and CLOSINGBALANCEMONTH can only return that (300).

    When you put the SUMX inside CLOSINGBALANCEMONTH, the expression gets evaluated per month, inside the time-intelligence context. That’s why the quarter correctly returns 100 — the last month’s value.

    So the root cause is:

    A VAR freezes the value too early → breaks time-intelligence.
    Inline evaluation lets CLOSINGBALANCEMONTH do its job.

    BTD with MTD Amount Test =
VAR Expr =
    SUMX(
        'Financial Data',
        'Financial Data'[BTD with MTD Amount lower] +
        1000 * 'Financial Data'[BTD with MTD Amount upper]
    )
RETURN
    CLOSINGBALANCEMONTH(
        CALCULATE(Expr),
        'Fiscal Period'[Fiscal Month]
    )

    This should work, please use this.


     

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    10 months ago

    Hi MBZA ,

    The issue is that variables in DAX are evaluated in the current filter context at the point they're defined, and they store a scalar value, not an expression.
    In your first version:
    At the Quarter level: res is calculated and returns a scalar value (300 - the sum of all three months in Q1)
    When CLOSINGBALANCEMONTH tries to work with res, it's just receiving the number 300, not an expression it can iterate over So it essentially treats this as a constant and sums it up for the quarter context

    In your second version:
    The CALCULATE(SUMX(...)) expression is not pre-evaluated. Instead, CLOSINGBALANCEMONTH receives the actual expression, which it can then evaluate in the context of the last month of each period.

    in the first expression try using a variable and see if you get the correct result.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi MBZA,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you Ahmed-Elfeel, Praful_Potphode and Poojara_D12 for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Icon for Community Support rankCommunity Support
      10 months ago

      Hi @MBZA,

       

      We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa