Cumulative Sum with condicion

[David-Menacho]

Hi i want to implement the next measure, which is similar to a cumulative sum but with a Max conditional

The formula for the measure is:

- first value is 0

- for the rest: value is Max Between the prev measure plus actual value minus a constant (in this case 1) And 0

Adding an example in text format

Date	Value	Measure
30/06/2021	1	0
01/07/2021	2	1
02/07/2021	5	5
03/07/2021	6	10
06/07/2021	4	13
07/07/2021	2	14
08/07/2021	5	18
09/07/2021	1	18
10/07/2021	4	21
11/07/2021	2	22

¿Someone has a solution for this?

¡Thanks for your help!

Regards

[Daniel29195 · Accepted solution]

@David-Menacho 

output : 

use the meaure below : 

this measure assume that you are reading the date from the same table , 

if not,  then simply change allselected(tbl_name[date])  to allselected(dimdate[date]) and orderby(dimdate[date], asc)

Measure 10 = 
var prev_value = 
CALCULATE(
    SUM('tbl_name'[Value]),
    OFFSET(
        -1,
        ALLSELECTED('tbl_name'[date]),
        ORDERBY('tbl_name'[date] , asc)
    )
)

return 
MAX(0,SUM('tbl_name'[Value]) - 1 + prev_value)

let me know if it works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[David-Menacho]

thanks! it was what i needed

[vanessafvg]

so are you saying you want the max value please provide an example of what right looks like and supply the data in text format.

[David-Menacho]

thanks for your time, i edited my question