{"id":3858,"date":"2021-12-23T10:46:02","date_gmt":"2021-12-23T18:46:02","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3858"},"modified":"2021-12-28T20:09:20","modified_gmt":"2021-12-29T04:09:20","slug":"create-a-date-table-with-fiscal-year-starting-on-any-date","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3858","title":{"rendered":"Create a Date Table with Fiscal Year Starting on Any Date"},"content":{"rendered":"\n
  1. Switch to Data view.<\/a><\/li><\/ol>\n\n\n\n
    \"Screenshot<\/figure>\n\n\n\n

    2. On the Home<\/strong> ribbon tab, from inside the Calculations<\/strong> group, click New Table<\/strong>.<\/p>\n\n\n\n

    \"Screenshot<\/figure>\n\n\n\n

    3. In the formula bar, enter the following, and then press Enter:<\/p>\n\n\n\n

    Date =
    \u200eCALENDARAUTO(6)<\/p>\n\n\n\n

    \"Screenshot
    The CALENDARAUTO() function returns a single-column table consisting of date values. The \u201cauto\u201d 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
    If the column does not appear, in the Fields<\/strong> pane, select a different table, and then select the Date<\/strong> table.<\/figcaption><\/figure>\n\n\n\n

    or you may want to use the CALENDAR function. like <\/p>\n\n\n\n

    DATE = CALENDAR (DATE (2020,07,01), DATE (2025,06,30))<\/p>\n\n\n\n

    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\u2019 data.<\/p>\n\n\n\n

    \"Screenshot<\/a>
    4. On the Table Tools<\/strong> contextual ribbon, from inside the Calculations<\/strong> group, click New Column<\/strong>.
    In the formula bar, type the following, and then press Enter<\/strong>:

    Year = \"FY\" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)<\/code>
    \"This

    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\u2019s 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.
    <\/a><\/figcaption><\/figure>\n\n\n\n
    \"Screenshot
    Verify that the new column was added.<\/figcaption><\/figure>\n\n\n\n

    5. Create the following two calculated columns for the Date<\/strong> table:<\/p>\n\n\n\n