Forum Discussion

David-Menacho's avatar
David-Menacho
Frequent Visitor
2 years ago
Solved

Cumulative Sum with condicion

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

 

DateValueMeasure
30/06/202110
01/07/202121
02/07/202155
03/07/2021610
06/07/2021413
07/07/2021214
08/07/2021518
09/07/2021118
10/07/2021421
11/07/2021222

 

¿Someone has a solution for this?

¡Thanks for your help!

 

Regards

 

Need Help
Tips and Tricks
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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! 🤠

     

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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! 🤠

     

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.