New column based on fixed amount

Question

Hi&nbsp;I've got two tables named Procurement and Stock.Procurement contains columns "Confirmed delivery date" and "Procurement Amount".Stock contains columns "Available stock", which is a current fixed amount. The tables are related based on "SKU" which is a part of both tables.&nbsp;So right now i've got a matrix table looking like this:&nbsp;Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Procurement Amount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Available StockSKU1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;718&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp;16. april 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;268&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp;14. may 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;151&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp;24. june 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;149&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp;4. july 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-377&nbsp;I want to create a column stating the available stock after the procurement amount and thereby being capable of possibly creating a date table where "New available stock" &gt; 0 in order to show the next date that entails available stock. So essentially something like:&nbsp;Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Procurement Amount&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Available Stock&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; New Available StockSKU1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;718&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp;16. april 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;268&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -109&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;14. may 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;151&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42(-109+151)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;24. june 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;149&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -377&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 191(42+149)&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;4. july 2021&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;150&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-377&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 341(191+150)&nbsp;And afterwards create a table visual stating next available stock date = 14. may 2021.&nbsp;Hope you can help.&nbsp;&nbsp;&nbsp;

selimovd · Answer

Hey&nbsp;Anonymous&nbsp;,&nbsp;when you have a column with the procurement amount and no other column, then it's hard to calculate the new amount. Do you have another column that shows the consumption of the product in the time frame?&nbsp;I also don't know what I should do with the column "Available Stock". You write -377 every row but I don't see that you really use that column.&nbsp;&nbsp;Could you explain your columns a little better?&nbsp;If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍&nbsp;Best regardsDenis&nbsp;Blog: WhatTheFact.bi&nbsp;

erd · Answer

Hello&nbsp;Anonymous&nbsp;,Well, from the examples you've provided here is what can be concluded:you have a constant here which is called&nbsp;Available Stock and = -377&nbsp;your new column calculation:first element = constant + current Procurement Amount (-377 + 268)all other elements =&nbsp;current Procurement Amount + previous row calculation resultIs this what you want to achive?

anonymous · Answer

Exactly&nbsp;ERD&nbsp;! 🙂

erd · Answer

&nbsp;If you want to have a Calculated DAX column that uses the previous row's calculated value as an input, thenDAX won't help you as DAX&nbsp;cannot do recursion. You can read the explanation here:&nbsp;Refer to previous row of same column.Power Query instead can be used to achieve the result.Here is the code that might help (it might be not the most elegant one, but it works):&nbsp;#"Added Index" = Table.AddIndexColumn(#"Previous step", "Index", 1, 1, Int64.Type),
#"Added CalcStep1" = Table.AddColumn(#"Added Index", "CalcStep1", each if [Index] = 1 then -377 + [ProcurementAmount] else [ProcurementAmount]),
#"Added Column" = Table.AddColumn(#"Added CalcStep1", "Result", each  List.Accumulate(List.Range(#"Added CalcStep1"[CalcStep1],0,[Index]),0,(state,current)=&gt; state+current)),
#"Removed Columns" = Table.RemoveColumns(#"Added Column",{"Index", "CalcStep1"})&nbsp;The idea here was to create an Index column, get the list of&nbsp;Procurement Amount values with&nbsp; the first element&nbsp;changed by constant and then calculate the result. Transitional columns may be deleted afterwards.In a table visual any filter can be used further (like Result &gt;0, etc.).&nbsp;&nbsp;Did I answer your question? Mark my post as a solution!

erd · Answer

Another approach is just sum all the previous&nbsp;Procurement Amount values from the start until the current one. In this case you can use DAX:&nbsp;Result = 
var firstDateValue = MIN(Table[Date])
var currentDate = Table[Date]
var constant = -377
var valueOnFirstDate = CALCULATE(MAX(Table[ProcurementAmount]) + constant, FILTER(Table, Table[Date] = firstDateValue))
var sumValue = CALCULATE(SUM(Table[ProcurementAmount]), FILTER(Table, Table[Date] &lt;= currentDate))
var result = IF(Table[Date] = firstDateValue, valueOnFirstDate, constant + sumValue)
return
result&nbsp;&nbsp;Did I answer your question? Mark my post as a solution!

Forum Discussion

New column based on fixed amount

5 Replies

Related content

Sequential Order column based on multiple criteria

Power Query Add rows of zero amount

Count based on revenue amount

Add duplicate row based on column

How to calculate the amounts based on the date