Dynamic Calendars - Application

Dynamic Calendars - Application

Building a dynamic calendar for our sample model

When to use

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

Instructions

Create the StartDate query:

• Go to Edit Queries > right-click the Staging-Sales table and choose Reference
• Rename the new query to StartDate
• Right-click the date column > Remove other columns
• Right-click the date column > Remove Duplicates
• Filter the date column > Is Earliest
• Select the date column > Transform > Year > StartOfYear
• Set the column’s data type to 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:

• Start by referencing the Staging-Budgets table
• 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

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

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