Hello,
I am struggeling with creating Custom Calendar that would allow me to use time intelligence function on data that is already aggregated by weeks and months. The original table with transactions contains over 20M rows, so for performance and space saving reasons the grouping is necceseary and I perform it whle querying the database in SQL Server. I want to create:
- Month vs Same Month Last Year
- Week vs Same Week Last Year
- Year-To-Date vs Last Year Year-To-Date (by Months)
- Year-To-Date vs Last Year Year-To-Date (by Weeks)
My idea was to assign some dummy dates to each row of data, and then create custom calendar that would have each of these dummy dats allong with other details. I just cannot figure out the key (how to create dummy date having Year number Month number and Week number - please keep in mind that for example Week 5 of 2020 is partialy in January and partialy in February)
Is it possible? Maybe I have to create 2 separete Calendars, one for Weeks and one for Months
