my report has fields: Item, Date, Demand, Inv, Inv Avail
I have Demand for the current month and the next 4
Available inventory = Damand - Inv
I have inventory for the current month and want future month inventory to = the previous month Inv Avail.
How can I get the value from a different record?
I think once I used Record.Field in a formula but can remember or find an example how.
Hi Ken,
You can select the rows from a previous step, here is an example:
Table.SelectRows(ChangedType, (x)=> [Item] = x[Item] and Date.Month([Date])=Date.Month(x[Date])-1)
x[Item] refers to current row, [Item] refers to the entire column from previous step.
You can add multiple conditions, I think you wanted a date condition, month=previous month.
You can add a column like:
AddColumn=Table.AddColumn(ChangedType, "Custom", each Table.Sort(Table.SelectRows(ChangedType, (x)=> [Item] = x[Item] and Date.Month([Date])=Date.Month(x[Date])+1),{{"Date", Order.Descending}}){0}[Inv])
Of course, you should deal with errors, as Date.Month(x[Date])+1 will not always return a valid month number (12+1 for example).
Another error should come from previous month missing.
