I am in need of some help on using custom dates across 2 data sources. I have not been able to find an answer that works for me.
I have one data source that is in an Access table. This data contains a series of event dates (load date, stop date, order date)
I have another data source in Excel that contains budget information. This data source lists every day of the year as well as an internal measure called a "business week". This business week is custom to the year, so in 2015 Week 1 is Thursday, Jan 1 through Saturday, Jan 3. Week 2 starts Sunday, Jan 4 and ends Saturday, Jan 10. The last week of the year (week 53) starts Sunday, Dec 27 and ends Thursday, Dec 31.
Most of the data I want to visualize is in the Access table. I want to look at data in the Access table using the custom business weeks as configured in my Excel sheet. How do I do that? The methods I've been using are not applying the business week to the dates in the Access table. Once I do this for one set of dates (load date, for example), I would also like to do it for stop date, order date, etc.
Hello Sean Campbell,
Did you find a resolution for this issue? Without a workbook, my suggestion would be to utilize Data Blending and select the Excel file as the primary data source. The Linking field between the data sources should be the date field since this is the field that exists in both sources. With the Excel file as the primary data source, the Business week field can be utilized within the view since it is at a higher level of aggregation than the lower level linking field of Date.
It may also be possible to attach to an Excel file directly from Access as a External data source. This will allow the live Excel file to be utilized with a Access Table to create a Join which will provide Row level connectivity between the two data sources and more easily enable the use of the defined Business Week field.
Let us know what you did and how it worked out.
I hope this helped.
Patrick
