cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MirzaAvdic Visitor
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.

 

COMPANYDATEAMOUNTINDICATOR
abc1-1-2019456NO
abc2-1-2019845NO
abc3-1-2019158NO
abc4-1-2019458NO
abc5-1-2019122YES
abc6-1-2019630NO
abc7-1-2019842NO
abc8-1-2019961YES
abc9-1-2019452YES
abc10-1-20191478YES
abc11-1-2019128NO
abc12-1-2019258NO
abc13-1-2019757NO
abc14-1-2019653NO

 

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.

 

COMPANYDATEAMOUNTINDICATORRunning total with conditional reset
abc1-1-2019456NO456
abc2-1-2019845NO1301
abc3-1-2019158NO1459
abc4-1-2019458NO1917
abc5-1-2019122YES0
abc6-1-2019630NO630
abc7-1-2019842NO1472
abc8-1-2019961YES0
abc9-1-2019452YES0
abc10-1-20191478YES0
abc11-1-2019128NO128
abc12-1-2019258NO386
abc13-1-2019757NO1143
abc14-1-2019653NO1796

 

Thanks in advance.

 

Regards,

Mirza

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV
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) 
    ) 
)

RunningReset.jpg

View solution in original post

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] )
)
 
Capture2.PNG
 
I apreciates a kudo. Thanks

View solution in original post

2 REPLIES 2
Super User IV
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) 
    ) 
)

RunningReset.jpg

View solution in original post

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] )
)
 
Capture2.PNG
 
I apreciates a kudo. Thanks

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors