Custom Calendar for sales data aggregated by weeks and months

Question

I am struggling 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 necessary and I perform it while 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 dates along 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 partially in January and partially in February)

Is it possible? Maybe I have to create 2 separate Calendars, one for Weeks and one for Months?

Przemyslaw Remin Przemyslaw Remin · Accepted Answer · 2019-11-18T16:13:45

Add calculated table:

Calendar = 
GENERATE (
    CALENDAR (
        DATE ( 2016,  1,  1 ),
        DATE ( 2020, 12, 31 )
    ),
    VAR VarDates = [Date]
    VAR VarDay = DAY ( VarDates )
    VAR VarMonth =  MONTH ( VarDates )
    VAR VarYear = YEAR ( VarDates )
    VAR YM_text = FORMAT ( [Date], "yyyy-MM" )
    VAR Y_week = YEAR ( VarDates ) & "." & WEEKNUM(VarDates)

    RETURN
        ROW (
            "day"     , VarDay,
            "month"   , VarMonth,
            "year"    , VarYear,
            "YM_text" , YM_text,
            "Y_week"  , Y_week
        )
)

You can customize it. In relation pane connect your Date field of FactTable (which is by week, as you say. This table can have missing dates and duplicate dates, to be precise) to the field Date of the Calendar table (which has unique Date, by day). Then, in all your visuals or measures always use the the Date field of the Calendar table. It is a good practice to hide the field Date in your FactTable.

More explanations here https://stackoverflow.com/a/54980662/1903793

Custom Calendar for sales data aggregated by weeks and months

1 Answers