dimDate = ADDCOLUMNS(
CALENDAR(DATE(2019, 1, 1), DATE(2021, 9, 26)), // Define the custom date range
"MonthID", MONTH([Date]),
"Month", FORMAT([Date], "MMM"),
"DayNumber", DAY([Date]),
"DayName", FORMAT([Date], "dddd"),
"DayID", WEEKDAY([Date], 2), // 2 means Monday as the start of the week
"Year", YEAR([Date]),
"Today", TODAY(),
// Add a SchoolYear column that adjusts the year based on the month
"SchoolYear", IF(
MONTH([Date]) >= 9,
YEAR([Date]) + 1,
YEAR([Date])
),
// Adjusts the month to fit the school year, starting from September
"SchoolMonthID", IF(
MONTH([Date]) >= 9,
MONTH([Date]) - 9,
MONTH([Date]) + 3
),
"Report Refresh", NOW() // We'll use this to display the report refresh date
)