Looking for some advice on the best way to tackle a scenario I need to display. I have a table with Sales with a date opened and date closed. What I would like to do is take the Average Days to Close for closed records and add that time to the Date Opened for Opened records to give a new dimension of Estimated Close. I then need to display my total sales by Est Date Closed.
I have two ways of solving this so far, add a calculated table limited to only open records of the type i define and add the column based on Date Opened + [Avg Days to Close] , then link to my dates table, or add a calculated column to my sales table. Pros/cons? Anyone have another idea on how to tackle? The need to display total over time I think restricts me to one of the above, but its been a long day