Cumulative sum (running total) on a measure in DAX is equal to zero

Question

I'm trying to implement a simple algorithm in PBI, at the end of which I need to calculate a cumulative sum (running total) of a measure (evaluated in memory as I understand it, not in the underlying physical table, and I need to use measures for flexibility - anyways considered best practice for lighter data model), and this is not working.

The measure in question is thus defined :

1)

SC_Montant_CAD_M_AjustPonctuel = 
IF(SUM(TransfertsTotaux_21C_Avoirs_Brut_GroupBy[Date_Remise]) > 1,
[delta_Volume_MT_toutesDim] * [SC_CoutMoyen_CAD_toutesDim_Offset],
0)

... where "Date_Remise" is a date criteria.

One syntax I'm trying to use to calculate the running total is this way :

2)

Ajustement_Cumule_3 = 
VAR DateMax = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois])
VAR Resultat = 
    CALCULATE(
        SUMX(TransfertsTotaux_21C_Avoirs_Brut_GroupBy, [SC_Montant_CAD_M_AjustPonctuel]),
            KEEPFILTERS('TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois] <= DateMax)
            )
    RETURN Resultat

where [SC_Montant_CAD_M_AjustPonctuel] is the measure for which I need to calculate the cumulative sum or running total, and I'm using SUMX() because apparently that's the "iterator" used for calculating sums...

For context, I've seen this definition of "DateMax" in several examples, but I don't think it's appropriate in my case because "DateMax" needs to be partitioned by other columns in table "TransfertsTotaux_21C_Avoirs_Brut_GroupBy". More specifically, in the algorithm I'm calculating running totals using the WINDOW() clause in CALCULATE(), where the table is ordered and partitioned in a specific manner :

3)

SC_Montant_CAD_M_toutesDim = 
    CALCULATE(
        [Montant_CAD_M_Total],
        WINDOW(
            0, ABS,
            0, REL,
            SUMMARIZE( 
                ALLSELECTED(TransfertsTotaux_21C_Avoirs_Brut_GroupBy)
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Type_Droit]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Comportement]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Juridiction]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Detention_Principale]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Id_CITSS]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois]
            ),
            ORDERBY(
                'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Type_Droit], ASC
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Comportement], ASC
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Juridiction], ASC
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Detention_Principale], ASC
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Id_CITSS], ASC
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois], ASC
            ),
            PARTITIONBY(
                'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Type_Droit]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Comportement]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Juridiction]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Detention_Principale]
                , 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Id_CITSS]
            )
        )
    )

On the first screen capture below, you can see that column "Ajustement ponctuel (M)" is correctly calculated (-0.3599 * 7.99 = -2.8762), but the cumulative sum that is supposed to calculated in column "Ajustement_Cumule_3" is equal to zero.

Screen capture 1 : with SUMX()

I think the zeros are generated by the SUMX() function, since using syntax 3) for the running total simply yields the original measure (2nd screen capture below) :

4)

Ajustement_Cumule_3 = 
VAR DateMax = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois])
VAR Resultat = 
    CALCULATE(
        [SC_Montant_CAD_M_AjustPonctuel],
        KEEPFILTERS('TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois] <= DateMax)
    )
RETURN Resultat

Screen capture 2 : without SUMX()

I have tried several other syntaxes to try to derive this simple result, for example in 5) :

5)

Ajustement_Cumule_333 = 
VAR _dateMax = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois] )
VAR _juridiction = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Juridiction] )
VAR _type_droit = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Type_Droit] )
VAR _comportement = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Comportement] )
VAR _id_citss = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Id_CITSS] )
VAR Resultat = 
    CALCULATE(
        SUMX(
            TransfertsTotaux_21C_Avoirs_Brut_GroupBy,
            [SC_Montant_CAD_M_AjustPonctuel]
            ),
            ALL(TransfertsTotaux_21C_Avoirs_Brut_GroupBy),
            'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois] <= _dateMax &&
            'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Juridiction] <= _juridiction &&
            'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Type_Droit] <= _type_droit &&
            'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Comportement] <= _comportement &&
            'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Id_CITSS] <= _id_citss
    )
RETURN Resultat

... but this can't work because the MAXes aren't partitioned, and anyway I still get the same result : zeros, probably because I'm using the iterator SUMX().

PS : While I am not a professional programmer I know this is a trivial task in any other developing environment, which is simply achieved by iterating (FOR or WHILE loops) over the columns of a table, array or dataframe. Why can't we simply iterate in DAX ?

Answer 1

Try it without the KEEPFILTERS

Ajustement_Cumule_3333 = 
VAR DateMax = MAX( 'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois])
VAR Resultat = 
    CALCULATE(
        SUMX(TransfertsTotaux_21C_Avoirs_Brut_GroupBy, [SC_Montant_CAD_M_AjustPonctuel]),
        'TransfertsTotaux_21C_Avoirs_Brut_GroupBy'[Date_Mois] <= DateMax
    )
RETURN Resultat