We have Sales data going back 20 years. To save space, processing, etc., I only show sales data that has been ordered in the last 5 years in Dundas. There are two main date fields that users would use to aggregate data, Order Date and Invoiced Date, to compare across years to create historical trends.
If they compare using OrderDt across years, everything is fine. If they compare using InvoiceDt, the totals for the early years are not accurate because they do not include the sales ordered before 5 years ago, but still invoiced in the last 5 years.
If I change my source data to include sales either ordered in the last 5 years OR invoiced in the last 5 years, that problem is solved but now if a user groups by the OrderDt, they may see like 10 lines in 2013, those lines that were invoiced in the last 5 years, but ordered in 2013. Now the Order Date aggregations are not entirely correct for the early years.
One solution would be to have two sales data sets, one limiting to Order Date in the last 5 years and one limiting to Invoice Date in the last 5 years. This would just be a training issue in trying to explain which one users should use and it is an almost doubling of data stored.
Another would be to show the last 10 years of data and just note to users that data is less accurate in the earlier years.
I would be interested in anyone has dealt with similar situations. The real situation is more complex of course because I already have several sales cubes (one showing detail records only for a few years, one showing summary information further back in time, etc.), but that is the basic issue.