Thanks very much for the clarification. I know what needs to be done now to make this work, but it seems the customer table is missing essential data that I’ll need to implement the solution. Here’s the scoop:
While the data model looks like a very simple star schema, what you are trying to do in terms of filtering the date from the customer table in effect would require a relationship between customer start date and sales date, but the relationship between the two tables is based on customer ID, which appears nowhere within the evaluation context of this analysis/calculation. In addition, the relationship between these two dates is many-to-many. There are a number of different ways to handle this, but I think the absolute simplest, and most effective from a performance standpoint is to pull the customer start date field into the sales table via the customer ID, and then create two inactive relationships from the date table to the sales table for the sales date and the customer start date, in effect implementing the events in progress pattern (shout out to @greg - the EIP writeup is terrific):
So far, so good. But the problem is when I try to pull this field over from the customer table, there are many customer IDs that appear in the sales table that do not appear in the customer table, making the complete merge impossible.
If you can please revisit your dataset and generate a complete customer table, we should be able to implement a solution quickly on this.
Please let me know if any of the above is unclear. I hope this is helpful.
- Brian