I have a fact table consisting of schedule data tracked over many months. An activity will have an entry for each month. I want to see if an activity started and finished on time by comparing each activity's start and finish dates to the previous month's forecasted start and finish dates.
Here is an example of what I want. The Started/Finished On Time columns are the calculated columns I want to make.
Example 1: Activity 1 did not start or finish on time in November, but did start on time in December. It did not finish on time so the Finished on Time column is marked "late".
Example 2: Activity 3 started earlier than was forecasted (12/7 start vs. 1/5 start). So it should be marked as starting early.
So pretty much the following needs to happen in the Started on Time column (similarly in Finished on Time column):
- For each row, find the same activity from last month and compare the start dates (I think I can figure the rest out but this is the real challenge at the moment)
- If the current row's start date is in the same month as the snapshot date and is in the same month as the forecasted start from the other record OR if there is no previous month's record, then return "On Time".
- Otherwise, if the current row's start is in the same month as the snapshot date but earlier than the start date from the previous month, return "Early"
- Otherwise, if the current row's start is in the future and the previous month's start date was in the future, return "Not Started"
- Otherwise, return "Late"
Is this even doable? I feel like this would require some careful use of LOOKUPVALUE and EARLIER, but I'm not sure how to go about it.
