Forum Discussion
New column based on fixed amount
Hello Anonymous ,
Well, from the examples you've provided here is what can be concluded:
- you have a constant here which is called Available Stock and = -377
- your new column calculation:
- first element = constant + current Procurement Amount (-377 + 268)
- all other elements = current Procurement Amount + previous row calculation result
Is this what you want to achive?
Exactly ERD ! 🙂
- ERD5 years ago
Community Champion
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 cannot do recursion. You can read the explanation here: 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):
#"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)=> state+current)), #"Removed Columns" = Table.RemoveColumns(#"Added Column",{"Index", "CalcStep1"})The idea here was to create an Index column, get the list of Procurement Amount values with the first element 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 >0, etc.).
Did I answer your question? Mark my post as a solution!
- ERD5 years ago
Community Champion
Another approach is just sum all the previous Procurement Amount values from the start until the current one. In this case you can use DAX:
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] <= currentDate)) var result = IF(Table[Date] = firstDateValue, valueOnFirstDate, constant + sumValue) return resultDid I answer your question? Mark my post as a solution!