Create a Date Table with Fiscal Year Starting on Any Date

Posted on by Summa Lai
  1. Switch to Data view.
Screenshot of the data view icon.

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

Screenshot of the New table button.

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.
Screenshot of the new created Date table.
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)
This image has an empty alt attribute; its file name is image-1.png

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