Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Thanks in advance.
Regards,
Mirza
Solved! Go to Solution.
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) ) )
Hi!
Here another alternative:
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.
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?
Hi!
Here another alternative:
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) ) )
I've been searching a lot for this solution. Thank you!!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |