Create a Date Table with Fiscal Year Starting on Any Date

Switch to Data view.

2. On the Home ribbon tab, from inside the Calculations group, click New Table.

3. In the formula bar, enter the following, and then press Enter:

Date =
‎CALENDARAUTO(6)

Screenshot of a new Date formula. — The CALENDARAUTO() function returns a single-column table consisting of date values. The “auto” behavior scans all data model date columns to determine the earliest and latest date values stored in the data model. It then creates one row for each date within this range, extending the range in either direction to ensure full years of data is stored.
This function can take a single optional argument which is the last month number of a year. When omitted, the value is 12, meaning that December is the last month of the year. In this case, 6 is entered, meaning that June is the last month of the year.

Notice the column of date values.

If the column does not appear, in the **Fields** pane, select a different table, and then select the **Date** table.

Screenshot of the new created Date table. — The CALENDARAUTO() function returns a single-column table consisting of date values. The “auto” behavior scans all data model date columns to determine the earliest and latest date values stored in the data model. It then creates one row for each date within this range, extending the range in either direction to ensure full years of data is stored.
This function can take a single optional argument which is the last month number of a year. When omitted, the value is 12, meaning that December is the last month of the year. In this case, 6 is entered, meaning that June is the last month of the year.

Notice the column of date values.

If the column does not appear, in the **Fields** pane, select a different table, and then select the **Date** table.

or you may want to use the CALENDAR function. like

DATE = CALENDAR (DATE (2020,07,01), DATE (2025,06,30))

At the bottom-left corner, in the status bar, notice the table statistics, confirming that 1826 rows of data have been generated, which represents five full years’ data.

Screenshot that shows the Date table has 1826 rows. — 4. On the **Table Tools** contextual ribbon, from inside the **Calculations** group, click **New Column**.
In the formula bar, type the following, and then press **Enter**:

`Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)`

A calculated column is created by first entering the column name, followed by the equals symbol (=), followed by a DAX formula that returns a single-value result. The column name cannot already exist in the table.
The formula uses the date’s year value but adds one to the year value when the month is after June. This is how fiscal years at Adventure Works are calculated.

This image has an empty alt attribute; its file name is image-1.png — 4. On the **Table Tools** contextual ribbon, from inside the **Calculations** group, click **New Column**.
In the formula bar, type the following, and then press **Enter**:

`Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)`

A calculated column is created by first entering the column name, followed by the equals symbol (=), followed by a DAX formula that returns a single-value result. The column name cannot already exist in the table.
The formula uses the date’s year value but adds one to the year value when the month is after June. This is how fiscal years at Adventure Works are calculated.

Screenshot to verify the new column was added. — Verify that the new column was added.

5. Create the following two calculated columns for the Date table:

Quarter
Month

Quarter =
‘Date'[Year] & ” Q”
& IF(
MONTH(‘DATE'[DATE]) <= 3,
3
IF(
MONTH(‘DATE'[DATE]) <= 6,
4
IF(
MONTH(‘DATE'[DATE]) <= 9,
1,
2
)
)
)

Month =
FORMAT(‘Date'[Date], “yyyy MMM”)

Screenshot that shows quarter and month columns have been added.

By default, text values sort alphabetically, numbers sort from smallest to largest, and dates sort from earliest to latest.

6. Add the MonthKey column to the Date table. This formula computes a numeric value for each year/month combination.

MonthKey = (YEAR(‘Date'[Date]) * 100) + MONTH(‘Date'[Date])

In the **Fields** pane, ensure that the **Month** field is selected (when selected, it will have a dark gray background).
7. On the **Column Tools** contextual ribbon, from inside the **Sort** group, click **Sort by Column** and then select **MonthKey**.

Screenshot that shows how to use the Sort by column. — 8. In the matrix visual, notice that the months are now chronologically sorted.

Screenshot that proves the months are now sorted correctly.

Ref: Lab – Introduction to DAX in Power BI Desktop – Learn | Microsoft Docs