Cumulative Sum column. Resets if row value = 0

Question

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:&nbsp; More than 30 region values (Madrid, Barcelona ...)# WEEK: W01, W02 ...&nbsp;# IN_LAST_8W: 1 if row in last 8weeks, else 0.# Incumple_dispo: 1 or 0&nbsp;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.&nbsp;&nbsp;&nbsp;I though I could achieve this with a cumulative column, but i cant...&nbsp;&nbsp;#&nbsp;CLUSTER_CUENTA&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;Incumple_dispo]&nbsp;);&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'&nbsp;);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[ENTORNO]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[ENTORNO]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;RED]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;RED]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;CLUSTER]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;CLUSTER]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;IN_LAST_8W]&nbsp;=&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;FY_WEEK_NUM]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;=&nbsp;EARLIER&nbsp;(&nbsp;'###&nbsp;DWT_Week_Cluster_combinada_acc'[#&nbsp;FY_WEEK_NUM]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;can you give me a hint? thanks in advance.

camargos88 · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;Check if this example helps you:&nbsp;Column = VAR _currentValue = 'Table'[Value]VAR _currentWeek = 'Table'[Week]VAR _sum = SUMX(FILTER('Table', 'Table'[Week] &lt;= _currentWeek), 'Table'[Value])VAR _last0 = CALCULATE(MAX('Table'[Week]), FILTER('Table', 'Table'[Week] &lt; _currentWeek &amp;&amp; 'Table'[Value] = 0))RETURN IF(_currentValue = 0, 0,SUMX(FILTER('Table', 'Table'[Week] &gt;= _last0 &amp;&amp; 'Table'[Week] &lt;= _currentWeek), 'Table'[Value]))&nbsp;

anonymous · Answer

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

Forum Discussion

Cumulative Sum column. Resets if row value = 0

2 Replies

Related content

Cumulative that reset

Resetting my cumulative total when it dips below zero

Cumulative Total

Matrix Column Order reseting

Cumulative sum that resets every year