How to sum FYTD across multiple years (selecting end month in slicer)

[Marcus2]

I currently have to select all months leading up to the end month (in my slicer) to get my FYTD totals.  I would like to just select the end month.  Is there a DAX measure to achieve this across multiple years (using my slicer to select the end month)?  Thanks

Product	FY	October	November	December	January	February	March	April	May	Jun	July	September
Apples	2023	5	5	5	5	5	5	5	5	5	5	5
Oranges	2023	5	5	5	5	5	5	5	5	5	5	5
Apples	2024	5	5	5	5	5	5	5	5	5	5	5
Oranges	2024	5	5	5	5	5	5	5	5	5	5	5
Apples	2025	5	5	5	5	5	5	5	5	5	5	5
Oranges	2025	5	5	5

[Marcus2]

Thanks, I think it works

[DataNinja777 · Accepted solution]

Hi @Marcus2 ,

To calculate Fiscal Year-To-Date (FYTD) totals by selecting only the end month in your slicer, you can use a DAX measure that dynamically determines the FYTD range based on the selected month and year. First, ensure you have a DateTable with columns such as Year, Month, Month Number, and Fiscal Year, and ensure it is linked to your data table. Then, create a measure to compute the FYTD total. This measure will calculate the total by filtering the data table for dates starting from the beginning of the fiscal year up to the selected month.

Here is the DAX measure:

FYTD Total = 
VAR SelectedMonth = MAX('DateTable'[Month Number])
VAR SelectedYear = MAX('DateTable'[Year])
VAR StartOfFiscalYear = DATE(SelectedYear - 1, 10, 1) 
VAR EndOfFiscalYear = DATE(SelectedYear, 9, 30)
VAR FilteredDates = 
    FILTER(
        ALL('DateTable'),
        'DateTable'[Date] >= StartOfFiscalYear &&
        'DateTable'[Date] <= EndOfFiscalYear &&
        'DateTable'[Month Number] <= SelectedMonth
    )
RETURN
CALCULATE(
    SUM('DataTable'[Value]), 
    FilteredDates
)

This measure works by first identifying the selected end month and year from the slicer. It then calculates the start and end dates of the fiscal year and filters the data table to include only dates within this range up to the selected month. The SUM function aggregates the values accordingly. With this measure, you only need to select the desired end month in the slicer, and the FYTD totals will be computed dynamically for the corresponding fiscal year. This approach eliminates the need to manually select all months leading up to the end month.

Best regards,