Dynamic DAX formula to change Financial year based off Current Date

[Austen229022]

Hi All, 

I am wanting to have a DAX measure/function that I can use in all my other DAX measures to find all data within the current FY.

The FY needs to be able to change when today's date moves into the next FY - ie: once the date hits 01/07/2025 it would change from FY24/25 to FY25/26.

Is this possible?

[DataNinja777 · Accepted solution]

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,

[Austen229022]

If I have the dates, and I am wanting a calculated column that says Yes if its it the current FY, how would this be done?

[Ashish_Mathur]

Hi,

You should create a Calendar Table and create this calculated column formula

Month number = month(calendar[date])

FY = if(calendar[month number]>=7,year(calendar[date])&"-"&year(calendar[date])+1,year(calendar[date])-1&"-"&year(calendar[date]))