Cumulative Sum column. Resets if row value = 0

[Anonymous]

Hello,

I am trying to simulate row iteration, suming al values = 1 given certain filters. I have the several columns:

# FY: Can be FY1920, FY2021, and so on...

# RED: Can be only "2G, 3G, 4G"

# Entorno: Total, Urbano and Rural.

# Cluster:  More than 30 region values (Madrid, Barcelona ...)

# WEEK: W01, W02 ... 

# IN_LAST_8W: 1 if row in last 8weeks, else 0.

# Incumple_dispo: 1 or 0

I need to calculate a column that sums all rows where Incumple_dispo=1, given some filters, but if Incumple_dispo=0, the sum resets.

I though I could achieve this with a cumulative column, but i cant... 

# CLUSTER_CUENTA =
CALCULATE (
    SUM ( '### DWT_Week_Cluster_combinada_acc'[# Incumple_dispo] );
    FILTER (
        ALL ( '### DWT_Week_Cluster_combinada_acc' );
        '### DWT_Week_Cluster_combinada_acc'[ENTORNO]
            = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[ENTORNO] )
            && '### DWT_Week_Cluster_combinada_acc'[# RED]
                = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# RED] )
            && '### DWT_Week_Cluster_combinada_acc'[# CLUSTER]
                = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# CLUSTER] )
            && '### DWT_Week_Cluster_combinada_acc'[# IN_LAST_8W] = 1
            && '### DWT_Week_Cluster_combinada_acc'[# FY_WEEK_NUM]
                <= EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# FY_WEEK_NUM] )
    )
)

can you give me a hint? thanks in advance.

[Anonymous]

hiya, thanks for solution! Is it possible to add one more condition that groups by another column?

[camargos88]

Hi @Anonymous ,

Check if this example helps you:

Column =

VAR _currentValue = 'Table'[Value]

VAR _currentWeek = 'Table'[Week]

VAR _sum = SUMX(FILTER('Table', 'Table'[Week] <= _currentWeek), 'Table'[Value])

VAR _last0 = CALCULATE(MAX('Table'[Week]), FILTER('Table', 'Table'[Week] < _currentWeek && 'Table'[Value] = 0))

RETURN IF(_currentValue = 0, 0,

SUMX(FILTER('Table', 'Table'[Week] >= _last0 && 'Table'[Week] <= _currentWeek), 'Table'[Value]))