Conditional Running Total with DAX

user_13112758 0
Viewed 547

I have a table with 2 columns: ID and Numbers; and I want to create a new column Result with the running total of the Numbers column but every time the running total surpass 20, the running total should be restarted. Also, if the running total is lower than 20, the result should not be displayed. This have to be done using DAX only.

Thanks in advice.

Note: the Conditional Running Total column exist just to show how the running total works.

ID Numbers ConditionalRunningTotal Result
1 9 9
2 7 16
3 9 25 25
4 8 8
5 9 17
6 9 26 26
7 1 1
8 0 1
9 0 1
10 9 10
11 9 19
12 2 21 21
13 1 1

Eddit: I added couple more rows to test the solution.

dax powerbi-desktop powerbi
1 Answers

I test this only on your sample data, there may be some errors in logic. I split this to several column, for better understanding of what i doing.

Running = var _curID = 'Table'[ID]
var _run = CALCULATE( sum('Table'[Numbers]), FILTER(all('Table'),  'Table'[ID] <= _curID)) 
return

ceiling(floor(ceiling(_run , 5) -
ceiling(MOD(_run, 5), 20), 15)
, 20) /20

Rung = var _curID = 'Table'[ID]
var  _previousDate = CALCULATE( max('Table'[Running]), FILTER('Table', 'Table'[ID] < EARLIER('Table'[ID])))
return
_previousDate+0


Final = var _id = [ID]
var _Rung = [Rung]
var _ret = 
CALCULATE( sum('Table'[Numbers]), filter(ALL('Table'), [ID] <= _id && _Rung = [Rung]))
return
if (_ret > 20, _ret, 0)

Final output:

enter image description here

user_143665380
Related