If you want the blending of the two datasources to occur at the Month - Year level, create on both datasource 2 new fields MONTH([Date]) and YEAR([Date]), and MONTH([Incident Date]) and YEAR([Incident Date]).
Convert those two calculated field as a Dimension and go in Data - Edit Relationship and add a custom relation ship between them.
Next remove the blending on Incident Date and apply it on Incident Date (Month) and Incident Date (Year) (The little orange chain symbol)
Finally, right-click on the SUM(Number of Records) axis , select Format , Pane - Special Values section , and set Marks at Hide(Connect lines)
Very nice, thanks! For some reason it's not showing all the points on the SUM(Number of Records) line though. I set it to always Mark Labels to see things easier. And the numbers that are there are correct. 3 for March 2012, 2 for July 2013, 5 for Sept. 2013, etc. But if you look at the underlying Albright Appts data there should be data points on just about every month instead of the gap from March 2012-July 2013, as well as the other smaller gaps.
And as a quick aside, why are the dates a month off? Take, on the axis, Feb. 2012. When i hover over the datapoint right above it it tells me that it's actually for March 2012.
Thanks again for your help here! It's much appreciated!
There is probably a much better answer to this but here is my solution:
1. Create a new data source with all possible dates found in Albright Appts and Albright System Notes.
2. Connect to that data source and start a new worksheet in your workbook.
3. Set up your relationships with the new data source for date and initial date etc.
4. Make sure the date in the new data source is a date and not datetime
5. Drag date from the new source to columns and continue to build the view
That's my method (attached a workbook), again, probably not the best method but it works.
EDIT: Also, the months aren't off, if you turn on tick marks you will be able to see it better. The label position can be a bit misleading.
What happens is because in this view, you selected System Notes as the Primary, a mark is placed for all Dates in Notes and if an Incident Date exist for that Date then it is displayed.
But if you want to display all incident Date and Mark the corresponding Date , just use appts as the Primary.
See in the attached
Or you can use Keshia solution if the blending is only on Date fields. It is not complicated to create a calendar datasource in Excel.
Jeff, if all of the dates in Albright System Notes were found in Albright Appts then Michel's solution would be fine and you would not need to make another data source. However, if you look at his solution, you are missing any dates in Albright System Notes that are not found in Albright Appts. For example, where is the value for December 1st, 2012 of 179,900?