topic Re: Calculated column that adds itself and other column in DAX in DAX Commands and Tips

Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Sun, 18 Sep 2022 17:31:18 GMT

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!

Re: Calculated column that adds itself and other column in DAX

Greg_Deckler — Sun, 18 Sep 2022 17:59:10 GMT

NachoSnchzCOYS You need an index or date column in order to define "previous". See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Sun, 18 Sep 2022 18:17:40 GMT

Hi! I tried to implement your solution but it doesnt work for me as the Excel formula I showed before. This is the result of doing what you told me to: Greg_Deckler

Re: Calculated column that adds itself and other column in DAX

Greg_Deckler — Mon, 19 Sep 2022 01:53:19 GMT

NachoSnchzCOYS Looking at this closer, since you want this to be a running total I guess, you will need to get rid of the MAXX for your __Previous variable (so, you will just have the FILTER statement) In the FILTER statement, make the = a <. Then, in your Return, make it __Current + SUMX(__Previous, [Value]).

The solution you implemented adds the current row to the previous row only. This new version will add the current row to all of the previous rows.

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 03:03:00 GMT

Hi NachoSnchzCOYS

please use

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

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 05:50:38 GMT

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

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 07:39:19 GMT

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] )

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 08:28:08 GMT

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.

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 09:23:07 GMT

NachoSnchzCOYS

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

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 09:35:58 GMT

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

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 10:18:09 GMT

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

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 10:28:14 GMT

NachoSnchzCOYS
Then propably there is no DAX solution for this. You need to try other tools.

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 10:56:09 GMT

Is it not possible to replicate that excel formula in DAX then?

Re: Calculated column that adds itself and other column in DAX

tamerj1 — Mon, 19 Sep 2022 11:23:14 GMT

NachoSnchzCOYS

Unfortunately, not possible

Re: Calculated column that adds itself and other column in DAX

Greg_Deckler — Mon, 19 Sep 2022 11:36:25 GMT

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

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 11:53:07 GMT

Okay, got it, thank you nevertheless. Do you think it would be possible to do it in Power Query? Because i might be able to do this calculations in powwer query instead of calculated columns

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 15:31:20 GMT

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

Re: Calculated column that adds itself and other column in DAX

Greg_Deckler — Mon, 19 Sep 2022 15:56:09 GMT

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

Re: Calculated column that adds itself and other column in DAX

NachoSnchzCOYS — Mon, 19 Sep 2022 16:11:45 GMT

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

Re: Calculated column that adds itself and other column in DAX

Greg_Deckler — Mon, 19 Sep 2022 16:28:16 GMT

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) )