create new column that will add the results to the previous cell of the column

[sdlx]

Hello,

I am trying to calculate the evolution of my stock.

I want to create a new column where the first row will be Custom.Free space + Out.

The next row will be the result of the previous row + Out.

With this I want to se the evolution of my free space (Custom.Free space) day after day with the toal of stock going out (Out).

Thank you for your help !

[v-eachen-msft]

Hi @sdlx ,

You could refer to the following DAX:

Column =
CALCULATE (
    SUM ( 'Table'[Out] ),
    FILTER ( 'Table', 'Table'[Week day] <= EARLIER ( 'Table'[Week day] ) )
)
    + LOOKUPVALUE (
        'Table'[Custom.Free space],
        'Table'[Week day], FIRSTDATE ( 'Table'[Week day] )
    )

If your "Costom.Free space" is always the same value, you could add this column directly.

[sdlx]

Hi,

Thank you for your quick answer, I tried but it doesn't work yet.

I might do someting wrong (I am a beginner right now):

I have copy paste your code in to the Custom column pop up and I just replace the 'Table' by the name of my table wich is 'Model HU leaving' , it give me an error message see print screen:

custom column

@v-eachen-msft  Can you tell me what I am doing wrong ?

Thank you very much

[JarroVGIT · Accepted solution]

You can try something like this as a calculated column:

Column = 
VAR curDate = Table[Week day]
VAR firstCustomFreeSpace = CALCULATE(SELECTEDVALUE(Table[Custom.Free space]), FILTER(ALL(Table), Table[Week day] = MIN(Table[Week day])))
RETURN
firstCustomFreeSpace + SUMX(FILTER(ALL(Table), Table[Week day] <= curDate), Table[Out])

It basically takes the first Custom.Free space value and adds the sum of all [Out] values upto (and including!) the current row. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[sdlx]

Hello,

Thank you for your answer !

I tried your formula in a custom column. I have replace Table by the nam of my table "Model HU leaving" (Was I suppose to do that ?)

It return a error message see print screen:

@JarroVGIT  Can you tell me what I am doing wrong ?

Thank you very much for your time !

[JarroVGIT]

There is a difference between a calculated column and a custom column. Our answers are calculated columns, you are trying to add a custom column.

See https://community.powerbi.com/t5/Desktop/Difference-between-custom-column-and-calculated-column/td-p...

Please mark this as the solution if it answers your question.

[sdlx]

Hi,

Thank you I learn something !

S I tried with the calculated column and here is the error message I get for your solution @v-eachen-msft :

And for your solution @JarroVGIT :

Do you see what could be the mistake ?

Thank you so much for your help !

[JarroVGIT]

In my case you see a red curly line below the = sign. You are missing the reference before that "= MIN()" bit on line 2 somewhat at the end.

Typing on phone, sorry. Look at my code again and you'll see you are missing a small bit (tablename[week num day]).

Please mark this as solution if this answers your question.

[sdlx]

Hi @JarroVGIT ,

I corrected my mistake, I feel that we are close to have a solution but I still have one error message about the SUMX that cannot work with value of type string:

Thank you so muche for your time !

[JarroVGIT]

Close indeed! Your column [Out] is of type Text rather then Whole Number or Decimal Number. You can change that in the query editor, see this:
https://www.tutorialgateway.org/change-data-types-of-a-column-in-power-bi/

[sdlx]

It work !

Thank you very muh @JarroVGIT