Forum Discussion
Repeated Calculated Column Optimization
Dear community, I have a table need to add several calculation columns.
Day0 = '1'[Qty]-'1'[Qty]/('1'[End_GroupQty_Previous]+'1'[VS_Qty])*'1'[Capacity]
Day1 =
VAR CurrentDate = '1'[MAV]
VAR CurrentVS = '1'[VS]
VAR CurrentLine = '1'[Line]
VAR Previous_Qty2 =
CALCULATE(
MAX('1'[Day0]),
FILTER(
'1',
'1'[vs] = CurrentVS &&
'1'[line] = CurrentLine &&
'1'[mav] < CurrentDate
),
TOPN(1,
FILTER('1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate),
'1'[mav],
DESC
)
)
RETURN
('1'[Qty] + Previous_Qty2) -
('1'[Qty] + Previous_Qty2) / ('1'[End_GroupQty_Previous] + '1'[VS_Qty]) * '1'[Capacity]
Day2 =
VAR CurrentDate = '1'[MAV]
VAR CurrentVS = '1'[VS]
VAR CurrentLine = '1'[Line]
VAR Previous_Qty2 =
CALCULATE(
MAX('1'[Day1]),
FILTER(
'1',
'1'[vs] = CurrentVS &&
'1'[line] = CurrentLine &&
'1'[mav] < CurrentDate
),
TOPN(1,
FILTER('1', '1'[vs] = CurrentVS && '1'[line] = CurrentLine && '1'[mav] < CurrentDate),
'1'[mav],
DESC
)
)
RETURN
('1'[Qty] + Previous_Qty2) -
('1'[Qty] + Previous_Qty2) / ('1'[End_GroupQty_Previous] + '1'[VS_Qty]) * '1'[Capacity]
The same logic for day2 applied on days3 and next by only change last row '1'[Qty] to '2'[Qty], '3'[Qty]…
In other words, in order to get Day9, I have to create columns next from Day3 to Day9.
Do we have other ways to use recurrence and create day9 directly? I Attached my sample file for your reference.
8 Replies
Hi NBU_FFF ,
The best approach to optimize your repeated Day0–Day9 calculated columns is to offload the recursive logic into Power Query using index-based row referencing. DAX is not well-suited for recursion over calculated columns because it lacks true looping behavior and cannot reference previously calculated column values in subsequent rows. Power Query, on the other hand, allows row-by-row processing using index columns and custom steps. You can start by sorting your table by [Line], [VS], and [MAV], then add an index column starting from 0. This index will serve as the “day” indicator, and you can reference the previous row's result for the recursive logic. After adding the index, you create a custom column such as [DayQty] using a recursive function. Here’s a simplified version of how the logic might look:
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], SortedRows = Table.Sort(Source,{{"Line", Order.Ascending}, {"VS", Order.Ascending}, {"MAV", Order.Ascending}}), AddIndex = Table.AddIndexColumn(SortedRows, "Day", 0, 1, Int64.Type), AddDayQty = Table.AddColumn(AddIndex, "DayQty", each null, type number), RecursiveCalculation = List.Accumulate( {0..Table.RowCount(AddDayQty)-1}, AddDayQty, (state, current) => let row = state{current}, prev = if current = 0 then null else state{current - 1}, qty = row[Qty], cap = row[Capacity], endGroupQty = row[End_GroupQty_Previous], vsQty = row[VS_Qty], prevQty = if current = 0 then null else prev[DayQty], calc = if current = 0 then qty - qty / (endGroupQty + vsQty) * cap else (qty + prevQty) - (qty + prevQty) / (endGroupQty + vsQty) * cap, newState = Table.ReplaceRows(state, current, {Record.TransformFields(row, {"DayQty", each calc})}) in newState ) in RecursiveCalculationThis logic processes the table row-by-row while calculating the DayQty recursively. The first row (Day0) uses your original formula, and all subsequent rows use the previous row’s DayQty to compute the current one. This way, you no longer need to manually create Day1 to Day9 columns in DAX. You end up with a single [DayQty] column that contains the recursive result for each day index, and the logic can be extended to as many days as needed without repetition.
Best regards,
DataNinja777 thanks a lot for your reply and explain. Unfortunately my data was combined from different sources and could not be processed in Power Query only. What's why I asked for the help with DAX.
Hi NBU_FFF ,
Apologies, my bad, DAX does not support true recursion, especially in calculated columns or tables where the value of a row depends on the value of a previous row in the same column. If you're trying to implement logic where each "DayN" calculation depends on the previous day’s result, DAX is not the right tool for this kind of recursion.
Best regards,
Hi NBU_FFF ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @NBU_FFF ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @NBU_FFF ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
