Dynamic Calendars - Theory

Dynamic Calendars - Theory

How to create a dynamic self-updating calendar for your model

When to use

When you need to build a calendar table on the fly that covers your entire date range

Instructions

Create a StartDate query:

• Go to Edit Queries and locate the table that will have the earliest possible date
• Right-click the table and choose Reference
• Rename your Query to StartDate
• Right-click the date column > Remove other columns
• Right-click the date column > Remove Duplicates
• If the column is not formatted as a date, change the data type to a Date type
• Filter the date column > Is Earliest
• Select the date column > Transform > Year > StartOfYear
• Set the data type as a date (even if it already is)
• Right-click the date > Drill Down
• On the left side, right-click the new query and clear the Enable Load checkbox

Create an EndDate query using the same steps as above except:

• Base it on the table that will have the latest possible date
• Filter the date column to Is Latest instead of Is Earliest
• Transform the Year to EndOfYear instead of StartOfYear

Create the Calendar query:

• Create a new Blank Query
• In the formula bar enter the following formula:

={Number.From(StartDate)..Number.From(EndDate)}

• Go to List Tools > Transform > ToTable
• Click OK with the default options
• Change the data type of Column1 to Date
• Rename Column1 to Date
• Add other date columns as desired
• Load the query and link it into your model

Your base calendar is now complete. To add new columns to the calendar:

• Select the Date column
• Go to Add Column -> Date
• Choose the Year, Month, Quarter, Week, or Day format you’d like to add
• Repeat from Step1 for each column you need to add

Hints & tips

• If you are going to perform any data analysis, you should ALWAYS have a proper Calendar table in your model
• Failure to have a good calendar can lead to misstated results due to gaps in your “fact” tables
• The StartDate and EndDate do not need to come from the same base query
• A good candidate for the EndDate is a Budget table, as budgets are typically prepared before the actual transactions occur