Go modeling tab
Create table
Enter below formula
CalendarTable =
VAR StartDate = DATE(2000, 8, 1) // Adjust the start of your fiscal year
VAR EndDate = DATE (2030, 7, 31) // Adjust the end of your fiscal year
RETURN
ADDCOLUMNS (
CALENDAR (StartDate, EndDate),
“Year”, YEAR ([Date]),
“Month”, MONTH ([Date]),
“MonthName”, FORMAT ([Date], “MMMM”),
“FiscalYear”, IF (MONTH ([Date]) >= 8, YEAR ([Date]) + 1, YEAR ([Date])),
“FiscalMonth”, IF (MONTH ([Date]) >= 8, MONTH ([Date]) – 7, MONTH ([Date]) + 5),
“Quarter”, “Q” & CEILING (IF (MONTH ([Date]) >= 8, MONTH ([Date]) – 7, MONTH ([Date]) + 5)/3, 1)
)
6. “FiscalYear” Column
“FiscalYear”, IF (MONTH ([Date]) >= 8, YEAR ([Date]) + 1, YEAR ([Date])),
The FiscalYear column adjusts the year based on whether the month is before or after August. The fiscal year starts in August, so:
If the month is August or later, the fiscal year is the following year (e.g., for August 2025, the fiscal year would be 2026).
If the month is before August (January through July), the fiscal year is the current year (e.g., for July 2025, the fiscal year would still be 2025).
7. “FiscalMonth” Column
“FiscalMonth”, IF (MONTH ([Date]) >= 8, MONTH ([Date]) – 7, MONTH ([Date]) + 5),
This column adjusts the month number based on the fiscal year:
If the month is August (8) or later, it subtracts 7 from the month number (so August becomes FiscalMonth 1, September becomes FiscalMonth 2, etc.).
If the month is January through July, it adds 5 to the month number (so January becomes FiscalMonth 6, February becomes FiscalMonth 7, etc.).
8. “Quarter” Column
“Quarter”, “Q” & CEILING(IF(MONTH([Date]) >= 8, MONTH([Date]) – 7, MONTH([Date]) + 5)/3, 1)
The Quarter column calculates the fiscal quarter:
First, it adjusts the month into the fiscal year months (as done in the FiscalMonth column).
Then, it divides the adjusted month by 3 to get the quarter (e.g., months 1–3 return 1st quarter, 4–6 return 2nd quarter, etc.).
CEILING(…, 1) rounds up the result to the next integer, ensuring that any fractional months (e.g., month 1/3 would round to 1) are placed in the correct fiscal quarter.
Finally, the “Q” prefix is added to denote the quarter (e.g., “Q1”, “Q2”).
Summary
The formula creates a Calendar Table with additional columns:
Year: The standard calendar year.
Month: The standard month number.
MonthName: The full name of the month.
FiscalYear: Adjusts the year based on the fiscal year starting in August.
FiscalMonth: Adjusts the month number based on the fiscal year.
Quarter: Divides the fiscal months into quarters, prefixed with “Q”.
This calendar table can be used in Power BI or other tools to handle fiscal periods, align financial reporting, or analyze data by fiscal year.