Extracting a field effective to specific date

Content

Hi all.

i'm creating an internal mobility and promotions OTBI report based on Assignment Event subject area. i'm filtering all "internal mobility" and "promotion" actions and extracting the employee's business unit and grade which are their values after the action. i want to extract at the same report the previous values of these employees business unit and grade so i can see what type of changes these process created.

i would be glad if someone can give me a hand in how to make it work. so far i only managed to calculate the day before the action was taken, which is effective to the previous instance of the employee. but how can i make an inside query on that date (which is not the date of the global query)?