Understanding the Date Dimension
Every AggieDB semantic model includes a shared Date dimension table. It's the foundation for all time-based analysis — filtering by month, comparing year-over-year, and calculating fiscal year metrics.
TAMU Fiscal Year
Texas A&M's fiscal year runs September 1 through August 31:
| Fiscal Year | Starts | Ends |
|---|---|---|
| FY 2025 | Sep 1, 2024 | Aug 31, 2025 |
| FY 2026 | Sep 1, 2025 | Aug 31, 2026 |
| FY 2027 | Sep 1, 2026 | Aug 31, 2027 |
This means October 2025 is in FY 2026, not FY 2025. Every AggieDB date calculation accounts for this.
Three Calendar Systems
TAMU operates on three overlapping calendar systems. The AggieDB Date table includes columns for all three so different departments can use whichever is relevant:
| Calendar | Start | End | Who Uses It |
|---|---|---|---|
| Standard Calendar | Jan 1 | Dec 31 | IT operations, general reporting |
| Fiscal Year | Sep 1 | Aug 31 | Budget, finance, IT procurement |
| Academic Year | Fall semester (late Aug) | Summer (Aug) | Academic departments, enrollment, student services |
Academic Terms
| Term | Months | Example |
|---|---|---|
| Fall | September – December | Fall 2025 |
| Spring | January – May | Spring 2026 |
| Summer | June – August | Summer 2026 |
The Academic_Year column spans Fall through Summer: AY 2025-26 covers Fall 2025 + Spring 2026 + Summer 2026.
Use the calendar system that matches your audience. IT reports typically use Fiscal Year. Academic reports use Academic Year/Term. General dashboards use Standard Calendar.
What's in the Date Table
The AggieDB Date table has one row per day (1,461 rows covering 4 years) with these key columns:
| Column | Example | What It's For |
|---|---|---|
Date_Key | 20260403 | Integer join key (YYYYMMDD format) |
Date | 2026-04-03 | Actual date value |
Calendar_Year | 2026 | Standard calendar year |
Calendar_Month | 4 | Month number (1–12) |
Calendar_Month_Name | April | Month name |
Calendar_Quarter | Q2 | Calendar quarter |
| Fiscal Year columns | ||
Fiscal_Year | FY 2026 | TAMU fiscal year (Sep 1 start) |
Fiscal_Quarter | FQ3 | Fiscal quarter |
Fiscal_Month | 8 | Month of fiscal year (Sep=1, Aug=12) |
| Academic Year columns | ||
Academic_Year | AY 2025-26 | Academic year (Fall start) |
Academic_Term | Spring | Fall (Sep-Dec), Spring (Jan-May), Summer (Jun-Aug) |
| Utility columns | ||
Calendar_Quarter | Q2 | Calendar quarter |
Fiscal_Year | FY 2026 | TAMU fiscal year (Sep 1 start) |
Fiscal_Quarter | FQ3 | Fiscal quarter |
Fiscal_Month | 8 | Month of fiscal year (Sep=1, Aug=12) |
Day_Of_Week | Thursday | Day name |
Is_Weekday | TRUE | Useful for filtering out weekends |
Is_Current_Month | TRUE | Dynamic flag for "this month" visuals |
Using the Date Table in DAX
Year-to-Date (Calendar)
Storage YTD =
TOTALYTD(
[Total Storage GB],
Date[Date]
)
Fiscal Year-to-Date
Storage FYTD =
TOTALYTD(
[Total Storage GB],
Date[Date],
"8/31" -- fiscal year ends August 31
)
The third argument "8/31" tells DAX that the year ends on August 31, making September 1 the start of the fiscal year.
Month-over-Month Change
Storage MoM % =
VAR CurrentMonth = [Total Storage GB]
VAR PriorMonth =
CALCULATE(
[Total Storage GB],
DATEADD(Date[Date], -1, MONTH)
)
RETURN
DIVIDE(CurrentMonth - PriorMonth, PriorMonth, 0)
Same Period Last Year
Storage SPLY =
CALCULATE(
[Total Storage GB],
SAMEPERIODLASTYEAR(Date[Date])
)
Common Patterns
Filtering to Current Fiscal Year
In a slicer or visual filter, use Date[Fiscal_Year] and select the current fiscal year. This automatically includes September through the current month.
Showing "Last 90 Days"
Last 90 Days =
CALCULATE(
[Total Storage GB],
DATESINPERIOD(Date[Date], TODAY(), -90, DAY)
)
Comparing Fiscal Quarters
Use Date[Fiscal_Quarter] on the X-axis of a chart to see trends across FQ1 (Sep–Nov), FQ2 (Dec–Feb), FQ3 (Mar–May), FQ4 (Jun–Aug).
What's Next?
Share your report with your team via Power BI App
Common measures and calculations for AggieDB models