SELECTCOLUMNS / SUMMARIZE broken in calculated table Whereas it works in EVALUATE in SSMS

[Anonymous]

I'm stumped, guys!

Both parts of this simple subtraction give the right result.

SQL Server version is 13.0.5337

For what it's worth I get the exact same result whether I use SUMMARIZECOLUMNS or ADDCOLUMNS and with or without a CALCULATE wrapping the measure.

[Stachu]

Qhat's the syntax for [PROFIT_CUM]? that's the measure that's gets evaluated incorrectly, correct?

[Anonymous]

It's a pretty simple one : it's the difference between two measures (see the screenshot between the two up there ^^ )

Both measures (INCOME_PLANNED_CUM and COST_CUM) evaluate correctly in the two 'environments'.

[Stachu]

hmm, for some reason I thought that the [PROFIT_CUM_CALC] is based on [PROFIT_CUM] as they were both inconsistent between the pictures.

It's a nested calculation, so without details on what's happening in the nested measures there is no much to investigate

[Anonymous]

The broken one is PROFIT_CUM.

PROFIT_CUM_CALC is where I recreate the measure using the aggregated 'base measures' instead of aggregating the PROFIT_CUM measure. Here's the code for the evaluate : 

evaluate 
selectcolumns(
    SUMMARIZE(FACT_COST_ACTIVITY,FACT_COST_ACTIVITY[PROJECT NAME]),
    "PROJECT NAME",[PROJECT NAME],
    "PROFIT_CUM",[PROFIT_CUM],
    "COST_CUM",[COST_CUM],
    "INCOME_PLANNED_CUM",[INCOME_PLANNED_CUM],
    "PROFIT_CUM_CALC",[INCOME_PLANNED_CUM]-[COST_CUM]
)

Here's the full breakdown of the broken measure : 

PROFIT_CUM := [INCOME_PLANNED_CUM]-[COST_CUM]

INCOME_PLANNED_CUM:= 
SUMX(
    VALUES(FACT_COST_ACTIVITY[ID_DIM_ACTIVITY]);
    VAR 
        LAST_DATES = 
        CALCULATE(
            MAX(FACT_COST_ACTIVITY[THE_DATE])
        )

RETURN
    CALCULATE(
        sum(FACT_COST_ACTIVITY[INCOME_PLANNED_CUM_COL]);
        DIM_DATE[THE_DATE] = LAST_DATES
    )
)

INCOME_PLANNED_CUM_COL :=
IF(
    FACT_COST_ACTIVITY[EXPENSE_ONLY_COL]; //BOOLEAN column in related dimension
    FACT_COST_ACTIVITY[COST_REAL_CUM]; //column
    FACT_COST_ACTIVITY[INCOME_BUDGET]  //column
    * 
    MIN(
        FACT_COST_ACTIVITY[QUANTITY_RATIO_CUM_COL];
        1
    )
)

QUANTITY_RATIO_CUM_COL :=
IF(
    FACT_COST_ACTIVITY[EXPENSE_ONLY_COL] = 1 && FACT_COST_ACTIVITY[QUANTITY_REAL_CUM] > 0;
    1;
    DIVIDE(
        FACT_COST_ACTIVITY[QUANTITY_REAL_CUM]; //column
        FACT_COST_ACTIVITY[QUANTITY_BUDGET_COL]; //column (in related table)
        0
    )
)

COST_CUM:=
SUMX(
    VALUES(FACT_COST_ACTIVITY[ID_DIM_ACTIVITY]);
    VAR 
        LAST_DATES = 
        CALCULATE(
            MAX(FACT_COST_ACTIVITY[THE_DATE])
        )

RETURN
    CALCULATE(
        sum(FACT_COST_ACTIVITY[COST_REAL_CUM]); //column
        DIM_DATE[THE_DATE] = LAST_DATES
    )
)
+ 
IF(not ISBLANK([QUANTITY_CUM]);0)