Additional Date Forms to Include in Your Date Hierarchy
When building date hierarchies in MicroStrategy or any analytics platform, most developers include the standard forms: Year, Quarter, Month, and Day. But there are several additional date forms that can dramatically improve the reporting experience for your users.
Beyond the Standard Date Hierarchy
A well-designed date hierarchy goes beyond the basics. Consider adding these additional date forms to make your reports more flexible and user-friendly:
Fiscal Calendar Attributes
- Fiscal Year: Essential for organizations whose fiscal year doesn’t align with the calendar year
- Fiscal Quarter: Mapped to your organization’s fiscal periods
- Fiscal Month: Particularly important for retail (4-4-5 calendars) or government fiscal years
Relative Date Forms
- Day of Week: Monday through Sunday — enables day-of-week trend analysis
- Week Number: ISO week number for weekly reporting
- Day of Year: Useful for year-over-year daily comparisons
- Is Weekend: Boolean flag for filtering business days vs. weekends
Display-Friendly Forms
- Month Name (Short): “Jan”, “Feb” — for chart axis labels
- Month-Year: “Jan 2024” — the most common reporting period label
- Quarter-Year: “Q1 2024” — for executive summaries
Business Logic Forms
- Is Holiday: Flag for your organization’s holiday calendar
- Is Business Day: Combines weekend and holiday logic
- Prior Year Same Day: Maps each date to its year-ago equivalent, accounting for day-of-week alignment
Implementation Tips
Build these forms into your date dimension table at the ETL level rather than calculating them in your BI tool. This ensures consistency across all reports and optimal query performance. Pre-calculating relative date flags (current month, prior year, etc.) in your ETL process can also significantly simplify report development.
Updated January 2021. This is a summary of the original article. Full content is being migrated from the previous site.