I have the following issue, where I need to show the previous Organization an Employee had worked on based on a Dateto field. This sounds quite straightforward but has been tough to crack!
I attached a PNG screenshot of what I need to accomplish since some folks here cannot download Excel files.
When the Dateto field is 12/31/9999 it is considered that the employee is currently working in an Organization. Any other date in the past is considered as a previous Organization the employee was assigned to.
I first tried using the Previous function but it didn’t work; the function only returns the last Organization in numerical order, so if the last Organization of an Employee is not in a sequence, the returned value will be incorrect. I need to factor in Dateto as well, in this variable.
I’ve tried to use RelativeValue after reading some tips around here, but I’ve been out of luck trying to get it running. Since the field I’m trying to find the last value for is a Dimension, I’m finding a little difficult to set the function working. Most of the times it returns blanks for every record, even if I slice in a seemingly correct manner. If anyone can provide any idea or suggestion it will be greatly appreciated.