Getting data from a previous date after filtering to a later date

[cdahl001]

Hello! I am trying to get the price of an item before it was put on markdown, but I have to filter the data to a certain date to see what price type it is first. While the filtered date is set, the dates the retail type happens can vary greatly. What do I need to put here to get the previous price? 

[danilang · Accepted solution]

hi @cdahl001 

In order to access values on other rows, you need to use the Multirow tool.

You also need to make sure that your data is sorted in the proper order, in this case by Item, Store and Start.  Once sorted, the multirow tool can access the value from the previous row giving an output that looks like this

With this method, you can set the WasPrice for every item that had rollback without filtering on the dates 

Dan

[cdahl001]

You're the man, Dan!

I keep forgetting the usefulness of sorting data into a funnel and then applying a formula that checks each row. It's working great now.