Calculated column that adds itself and other column in DAX

[NachoSnchzCOYS]

Hi! I think this is pretty easy but i cant get through it by myself. I'm showing in Excel what i want to do in DAX. I need to create the calculated column A2 that, for the actual row adds its previous value and the actual value of A1. 

Thanks in advance!

[tamerj1]

Hi @NachoSnchzCOYS 

please use

Column =
VAR CurrentIndex = Test1[Index]
VAR T1 =
FILTER ( Test1, Test1[Index] <= CurrentIndex )
RETURN
SUMX ( T1, Test1[Value] )

[NachoSnchzCOYS]

Hi! The issue with this solution is that I don't want to sum all the previous rows, just the previous one, as it appears in the excel screenshot I shared before 

[tamerj1]

@NachoSnchzCOYS 
And the previous one is the sum of its previous one and so on. You need to think outside the box. Check the sample file

A2 = 
VAR CurrentIndex = Test1[Index]
VAR T1 =
    FILTER ( Test1, Test1[Index] <= CurrentIndex )
RETURN
    SUMX ( T1, Test1[A1] )

[NachoSnchzCOYS]

Hi, you are right, that should be the correct solution. The thing is that i had another restrictions that i forgot to mention. I'll show again what i need to implement with an excel formula screenshot:

As you can see i have restrictions at 0 and 7500 so I cant simply add all the previous rows because it should kind of "reset" when the sum its lower than zero. 

[Greg_Deckler]

@NachoSnchzCOYS You will need a resetting calculation with criteria in this case. The solution for a resetting calculation with criteria is Cthulhu. Cthulhu - Microsoft Power BI Community

[NachoSnchzCOYS]

I see how this could help me but i cant figure out how to use criteria in my own case, as i only have positive or negative values, not categories @Greg_Deckler 

[Greg_Deckler]

@NachoSnchzCOYS If you provide your sample data as text in a table, I'll see what I can put together. Always fun revisiting Cthulhu. There is also Bride of Cthulhu which might also provide some guidance. (2) Bride of Cthulhu! - Microsoft Power BI Community

[NachoSnchzCOYS]

Than you in advance, i dont know how i could export the data so i just pasted it here. Let me know if you need it in a different way! (This is a sample of the data, jus 50 rows) @Greg_Deckler 

Index Value
1 -954,411075901891
2 -2500
3 -2500
4 -2500
5 -2500
6 -2500
7 -2500
8 -2500
9 -2500
10 0
11 1292,62496353546
12 2500
13 2500
14 2500
15 2500
16 2500
17 2500
18 -1271,39435593431
19 -2500
20 -1922,67192230658
21 -738,409771717168
22 0
23 0
24 0
25 0
26 0
27 -143,049757979741
28 -397,091991513815
29 -431,30682926693
30 -120,203415804044
31 -364,388685324651
32 -308,09400236181
33 0
34 0
35 2500
36 2500
37 2500
38 2500
39 2500
40 2500
41 2500
42 -969,057069732473
43 -2500
44 -1907,42730128896
45 0
46 0
47 611,378249537367
48 320,513151761163
49 -776,089147254734
50 -2500

[Greg_Deckler]

@NachoSnchzCOYS OK, give this a shot:

Column = 
    VAR __Value = [Value]
    VAR __Index = [Index]
RETURN
    IF( __Value <= 0,
        0,
            VAR __MaxIndex = MAXX(FILTER('Table',[Value] <= 0 && [Index] < __Index),[Index]) + 1
            VAR __Sum = SUMX(FILTER('Table',[Index] <= __Index && [Index] >= __MaxIndex),[Value])
        RETURN
            IF(__Sum >= 7500, 7500, __Sum)
    )

[NachoSnchzCOYS]

This is closer but it doesnt substract when the value is negative.

[Greg_Deckler]

@NachoSnchzCOYS Still not sure I'm 100% on the rules here, can you please elaborate on the specific rules for this.

[NachoSnchzCOYS]

Okay, I can give a daily example, the model is like a debit card and the column value are the income (positive ones) or the bills ( negative ones). The model has to do the accumulate but it cant go lower than 0 or higher than 7500. 

I think this screenshot with the excel formula shows it pretty well. 

Thanks in advance, i'm sure this time you'll get it @Greg_Deckler 

[Greg_Deckler]

@NachoSnchzCOYS Try this as two columns, Reset and Column:

Reset = IF([Value] = 0,1,0)


Column = 
    VAR __Value = [Value]
    VAR __Index = [Index]
    VAR __MaxIndex = MAXX(FILTER('Table',[Value] <= 0 && [Index] < __Index),[Index])

    VAR __LastReset = MAXX(FILTER('Table',[Index] < __Index && [Reset] = 1),[Index])

    VAR __MaxIndexValue = MAXX(FILTER('Table',[Index] = __MaxIndex),[Value])
    
    VAR __MaxIndex2 = MAXX(FILTER('Table',[Value] > 0 && [Index] < __Index),[Index]) + 1

    VAR __Sum = SUMX(FILTER('Table',[Index] <= __Index && [Index] > __LastReset),[Value])
    VAR __Sum1 = IF(__Sum >= 7500, 7500, __Sum)
    VAR __Sum2 = SUMX(FILTER('Table',[Index] <= __Index && [Index] >= __MaxIndex2),[Value])
    VAR __Sum3 = __Sum1 + __Sum2
RETURN
    SWITCH(TRUE(),
        [Value] = 0, 0,
        ISBLANK(__LastReset) && [Value] < 0,0,
        __MaxIndexValue = 0 && [value]>0, __Sum1,
        [Value] < 0 && __Sum3 > 0, __Sum3,
        __Sum1 >= 0, __Sum1,//__Sum,
        0
    )

[NachoSnchzCOYS]

That is pretty close to what im looking for! Thank you. 

Here you can see what that is doing right now, and the only thing that is still missing is when value is 0 the column should remain the same amount. Idk if its possible @Greg_Deckler 

[NachoSnchzCOYS]

@Greg_Deckler 

[Greg_Deckler]

@NachoSnchzCOYS Pretty certain this is recursive at this point which means you won't be able to solve this with DAX. It is recursive because of your minimums and maximums. You have to compute a value at a point in time and then maintain that value. I'll give it some more thought but you are pretty much stuck with having to know your "previous value" from a calculation and once you are there, it's pretty much recursion and I've tried everything in the world to solve that problem and have never been able to. Note that Cthulhu is not recursive because you are actually just checking if something has changed from one state to another, defining the reset point and performing the calculation. Your case is not that.

[NachoSnchzCOYS]

Well I appreciate a lot all of your help nevertheless. It might be useful even not being the correct solution so thank you again. If you come up with something else please let me know! Regards @Greg_Deckler 

[tamerj1]

@NachoSnchzCOYS 

Reset is very complex. Let me see what can I do

[tamerj1]

@NachoSnchzCOYS 
How many times the +ve to -ve to +ve cycle can reapeat?

[NachoSnchzCOYS]

I don't understand. It's always changing from positive to negative. It should be as that excel formula but I know it isn't easy