Strange CLOSINGBALANCE behaviour

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)