Dynamic DAX formula to change Financial year based off Current Date

Hi Austen229022 ,

Creating a proper Calendar table with a fiscal year ending on June 30 is the best and most scalable way to handle time intelligence in DAX. When you structure your Calendar table to reflect your fiscal year setup, all the time-based measures like YTD, PY, MTD, and rolling periods become much easier and more accurate, without needing to manually adjust filters or hack around DAX limitations.

Here’s how you can create a Calendar table where the fiscal year starts on July 1st and ends on June 30th:

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2018,7,1), DATE(2028,6,30)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Fiscal Year", 
        "FY" & 
        IF(
            MONTH([Date]) >= 7,
            YEAR([Date]) + 1,
            YEAR([Date])
        ),
    "Fiscal Month Number",
        IF(
            MONTH([Date]) >= 7,
            MONTH([Date]) - 6,
            MONTH([Date]) + 6
        ),
    "Fiscal Quarter",
        "Q" & 
        SWITCH(
            TRUE(),
            MONTH([Date]) >= 7 && MONTH([Date]) <= 9, 1,
            MONTH([Date]) >= 10 && MONTH([Date]) <= 12, 2,
            MONTH([Date]) >= 1 && MONTH([Date]) <= 3, 3,
            MONTH([Date]) >= 4 && MONTH([Date]) <= 6, 4
        )
)

This creates a calendar with extra columns for fiscal year, fiscal month number (where July is month 1), and fiscal quarter (where July to September is Q1). You should link this table to your fact tables using the Date column. Once that's set up, you can use DAX time intelligence functions that respect the fiscal year end by specifying it explicitly. For example, to calculate Year to Date sales based on a June 30 fiscal year end, you can write:

Sales YTD Fiscal = 
CALCULATE(
    SUM('Sales'[Amount]),
    DATESYTD(
        'Calendar'[Date],
        "6/30"
    )
)

By including "6/30" in the DATESYTD function, DAX knows to treat July 1 as the beginning of the year. This approach is much more robust than relying on hacks or custom slicers, and it future-proofs your model so it continues working properly as time moves forward. If you'd like, I can also help modify this Calendar table to dynamically extend its start and end dates based on your data instead of hardcoding the range.

Best regards,