Method 1 in The Cross Join Collection bins dates into months, years, or whatever bin size needed.
The Custom SQL in attached workbook is this:
SELECT * FROM [data$] d, [lookup$] l WHERE d.[Start Date] <= l.[Month End] and d.[End Date] >= l.[Month Start]
You need to open Excel with the Legacy Connector:
Here is screenshot from attached workbook:
If you only need to bin dates into years, then you could change the date intervals into year periods and add a calculation for number of months and thus reduce the number of rows from up to 12 to 1 per year.
Ps. This is a commonly asked question as documented in FAQ: Open & Close Dates. If you wish this could be done without custom SQL, then you might like to up-vote Date period dimension type with built-in row split. See also other ideas at the bottom of the FAQ: Open & Close Dates page.
Attached Workbook Version: 8.2
Thanks!!! That's exactly what I am looking for!