Active Member
March 21, 2019
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
1 Guest(s)