I've searched high and low for my question, I've seen bits and pieces that have helped me define it more but I don't think I've found exactly what I am looking for.
I have a data model that is a hub and spoke. The center is a sales transaction table with a date column, several measures (price, cost, etc.), and ID columns that link to Dimenesion Outline tables (product, channel, etc. hierachies). There is also a calendar table that has a date column, calendar year/quarter/month/week/day columns, and fiscal year/quarter/month/week/day columns (4-4-5 and not ending in Dec) , and I've thrown both of those calendars into hierachies. This works great if you're just looking at period totals for something like a line graph, but not so much where time intelligence is concerned.
What I'm going for is common time intelligence functions (*-to-date and trailing x periods) that will work for both calendar bases but won't require each measure (there are currently 20+) to be recreated.
A good end result, someone would coudl have a matrix/pivot table with time periods in the columns, a dimension outline on the rows, a few measures in the data, and easily flip the numbers between YTD, TTM, etc. Also the possiblity of filtering on a single time period and stringing the time intelligence across the rows.
I've seen this https://exceleratorbi.com.au/toggle-overlapping-time-periods-using-calendar-table/ but am not quite sure it is feasible to dynamically build a recursive table when I am keying on days or weeks across 8+ years. The numbers table is closing in on 1m rows so I want to be careful the file stays useable.
Thoughts on how this could be structured efficiently?
