Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MirzaAvdic
Regular 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
jdbuchanan71
Super User
Super User

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

mauriciosotero
Resolver III
Resolver III

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

6 REPLIES 6
jdbuchanan71
Super User
Super User

@GidgetRae 

You can just change that line to do two compares.  This works the same:

 

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' ), 
        'Table'[DATE] >= _StartDate && 'Table'[DATE] <= _RowDate
    ) 
)

 

So in your data it would be like this

'Time Detail'[Index2] >= _StartIndex && 'Time Detail'[Index2] <= _RowIndex

@jdbuchanan71 Thank you, that worked perfectly! I knew it had to be something simple, I had just been staring at it all for too long.

GidgetRae
Frequent Visitor

This solution is exactly what I need for a problem I've been stuck on for weeks now but for the very last line 

DATESBETWEEN('Table'[DATE], _StartDate, _RowDate)

I need something along the lines of [FUNCTION??]('Time Detail'[Index2], _StartIndex, _RowIndex) Since I'm using an Index instead of dates I can't use the DATESBETWEEN function and haven't successfully found something to replace it.

 

Any ideas?

mauriciosotero
Resolver III
Resolver III

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
jdbuchanan71
Super User
Super User

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

I've been searching a lot for this solution. Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.