Cumulative Total by Month Working for one measure and not another

[Brotedo]

Hello,

I have a report that uses multiple cumulative totals by Year-to-Date month. The formula I've been using over and over is this:

YTDSumAdjUnadj = 
VAR DateMaxYTD =
    MAX ( 'FactTable'[YTDMonthNumber] )
RETURN
    SUMX (
        CALCULATETABLE (
            'FactTable',
            REMOVEFILTERS ( 'FactTable' ),
            VALUES ( 'FactTable'[UserId] ),
            'FactTable'[YTDMonthNumber] <= DateMaxYTD && 'FactTable'[YTDMonthNumber]>=1
        ),
        [UnadjInclusiveSum]
    )  

Where [UserId] connects to my User Key table, and [YTDMonthNumber] just assigns 1 for Jan, 2 for Feb, etc.

This formula has worked for every measure I've used so far, except this one:

YTDRecovery = 
VAR DateMaxYTD =
    MAX ( 'FactTable'[YTDMonthNumber] )
RETURN
    SUMX (
        CALCULATETABLE (
            'FactTable',
            REMOVEFILTERS ( 'FactTable' ),
            VALUES ( 'FactTable'[UserId] ),
            'FactTable'[YTDMonthNumber] <= DateMaxYTD && 'FactTable'[YTDMonthNumber]>=1
        ),
        [Recovery]
    )   

Where recovery is built like this:

Recovery = calculate([Unadjusted],filter(FactTable, FactTable[Recovery]=true))

Unadjusted = sum('FactTable'[UnadjustedK])

Where UnadjusedK is just a calculated column of values.

For some reason, Recovery is the only measure where that formula does not create a cumulative sum:

[ChenwuZhu_Gmail]

Hi @Brotedo ,

The problem is the CALCULATE() in [YTDRecovery].

CALCULATE will modify filter context, it means when you evaluate the [Recovery] in [YTDRecovery], it will ignore the filter you defined before.

So, you need add the filter "FactTable[Recovery]=True" to the CALCULATETABLE().

Maybe you can try this code:

YTDRecovery =
VAR DateMaxYTD =
    MAX ( 'FactTable'[YTDMonthNumber] )
RETURN
    SUMX (
        CALCULATETABLE (
            'FactTable',
            REMOVEFILTERS ( 'FactTable' ),
            VALUES ( 'FactTable'[UserId] ),
            'FactTable'[YTDMonthNumber] <= DateMaxYTD
                && 'FactTable'[YTDMonthNumber] >= 1,
            FILTER ( FactTable, FactTable[Recovery] = TRUE )
        ),
        [Recovery]
    )

Best regards.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Hi @Brotedo 

If your model is a one-fact-table model (as it looks to be), I'd kindly suggest you change it to a proper star schema as quickly as you can before you are not able to explain what the model calculates. Seriously. One-table models inevitably lead to errors that you won't even be aware of and numbers you won't be able to explain.