cancel
Showing results for
Did you mean:
Highlighted
Visitor

## How to calculate running total by row with a conditional reset

Hi guys,

I ran into a question that seemd pretty easy to answer but seems far from easy. Or better, I will let you judge 🙂

Looking at the tabel below, I need to calculate (calculated column or measure for the table visual) the running total with the reset condition.

 COMPANY DATE AMOUNT INDICATOR abc 1-1-2019 456 NO abc 2-1-2019 845 NO abc 3-1-2019 158 NO abc 4-1-2019 458 NO abc 5-1-2019 122 YES abc 6-1-2019 630 NO abc 7-1-2019 842 NO abc 8-1-2019 961 YES abc 9-1-2019 452 YES abc 10-1-2019 1478 YES abc 11-1-2019 128 NO abc 12-1-2019 258 NO abc 13-1-2019 757 NO abc 14-1-2019 653 NO

The reset condition is based on IDICATOR column. If it states NO then the normal running total on AMONT should be performed but if it states YES then the calculation should show a 0; for each row that states YES. The next row with NO should start the running total from scratch. The result should look like in the table below.

 COMPANY DATE AMOUNT INDICATOR Running total with conditional reset abc 1-1-2019 456 NO 456 abc 2-1-2019 845 NO 1301 abc 3-1-2019 158 NO 1459 abc 4-1-2019 458 NO 1917 abc 5-1-2019 122 YES 0 abc 6-1-2019 630 NO 630 abc 7-1-2019 842 NO 1472 abc 8-1-2019 961 YES 0 abc 9-1-2019 452 YES 0 abc 10-1-2019 1478 YES 0 abc 11-1-2019 128 NO 128 abc 12-1-2019 258 NO 386 abc 13-1-2019 757 NO 1143 abc 14-1-2019 653 NO 1796

Regards,

Mirza

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV

## Re: How to calculate running total by row with a conditional reset

Hello @MirzaAvdic

I was able to get a calculated column to work with this.

```Running total with conditional reset =
VAR _RowDate = 'Table'[DATE]
VAR _EarlierReset =
CALCULATE( MAX ( 'Table'[DATE] ), ALL('Table'),'Table'[DATE]<= _RowDate,'Table'[INDICATOR]="YES")
VAR _FirstTableDate =
CALCULATE(MIN('Table'[DATE]),ALL('Table'))
VAR _StartDate =
IF ( ISBLANK ( _EarlierReset ), _FirstTableDate, _EarlierReset + 1 )
RETURN
IF ( 'Table'[INDICATOR] = "YES", 0,
CALCULATE (
SUM ( 'Table'[AMOUNT] ),
ALL ( 'Table' ),
DATESBETWEEN('Table'[DATE], _StartDate, _RowDate)
)
)```

Member

## Re: How to calculate running total by row with a conditional reset

Hi!

Here another alternative:

Running total calculated =
VAR CurrentDate = Planilha1[DATE]
VAR InicialDate = CALCULATE(MAX(Planilha1[DATE]);FILTER(Planilha1;Planilha1[DATE]<CurrentDate && Planilha1[INDICATOR] = "YES"))
RETURN
IF( Planilha1[INDICATOR] = "YES"; 0 ;
SUMX ( FILTER(Planilha1;Planilha1[DATE]<=CurrentDate && Planilha1[DATE] > InicialDate);Planilha1[AMOUNT] )
)

I apreciates a kudo. Thanks
2 REPLIES 2
Super User IV

## Re: How to calculate running total by row with a conditional reset

Hello @MirzaAvdic

I was able to get a calculated column to work with this.

```Running total with conditional reset =
VAR _RowDate = 'Table'[DATE]
VAR _EarlierReset =
CALCULATE( MAX ( 'Table'[DATE] ), ALL('Table'),'Table'[DATE]<= _RowDate,'Table'[INDICATOR]="YES")
VAR _FirstTableDate =
CALCULATE(MIN('Table'[DATE]),ALL('Table'))
VAR _StartDate =
IF ( ISBLANK ( _EarlierReset ), _FirstTableDate, _EarlierReset + 1 )
RETURN
IF ( 'Table'[INDICATOR] = "YES", 0,
CALCULATE (
SUM ( 'Table'[AMOUNT] ),
ALL ( 'Table' ),
DATESBETWEEN('Table'[DATE], _StartDate, _RowDate)
)
)```

Member

## Re: How to calculate running total by row with a conditional reset

Hi!

Here another alternative:

Running total calculated =
VAR CurrentDate = Planilha1[DATE]
VAR InicialDate = CALCULATE(MAX(Planilha1[DATE]);FILTER(Planilha1;Planilha1[DATE]<CurrentDate && Planilha1[INDICATOR] = "YES"))
RETURN
IF( Planilha1[INDICATOR] = "YES"; 0 ;
SUMX ( FILTER(Planilha1;Planilha1[DATE]<=CurrentDate && Planilha1[DATE] > InicialDate);Planilha1[AMOUNT] )
)

I apreciates a kudo. Thanks

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!