Calculate Fiscal Year To Date value using a column as filter

[ljx0648]

Dear Community,

My company's fiscal year starts from Nov 1st each year and ends with Oct 31st as the last date every year.

For example, Fiscal year 2023 starts from Nov 1st, 2022 and ends with Oct 31st, 2023.

For all the prior years, I am using a Fiscal Year to Date dax formula with no issue:

FYTD_Sales = Calculate (sum(salestable[sales]),DatesYTD(datetable[date],"31/10")

However, I got a special request from my boss:

For Fiscal year 2024, we need to add Nov 1st, 2024 as the last date of the Year WITHOUT impacting all calculations of the prior years.

In other words, For ALL the fiscal prior fiscal 2024, I can still use the formula above.

But for fiscal 2024 I will need to create another formula from Nov 1st, 2023 to Nov 1st 2024. 

One of the solutions I can think of is to create a Fiscal Year Column in the data table and try to use that column to sum up Fiscal year to date value.

Sample data and desired result (YTD Value) below:

Fiscal Year	Year_Month	Value	YTD Value
2023	Jun-23	1	1
2023	Jul-23	1	2
2023	Aug-23	1	3
2023	Sep-23	1	4
2023	Oct-23	1	5
2024	Nov-23	1	1
2024	Dec-23	1	2
2024	Jan-24	1	3
2024	Feb-24	1	4
2024	Mar-24	1	5
2024	Apr-24	1	6
2024	May-24	1	7
2024	Jun-24	1	8
2024	Jul-24	1	9
2024	Aug-24	1	10
2024	Sep-24	1	11
2024	Oct-24	1	12
2024	Nov-24	1	13

May I know if someone has an idea about how to use the Fiscal Year column to get the YTD Value using DAX as per the example displayed above?

Any tips are appreciated.

Thank you!

[shafiz_p · Accepted solution]

Hi @ljx0648  

Create a fiscalYear column in your datetable. Try the below code:

FiscalYear = IF(
    Calendar[Month] >= 11,
    Calendar[Year] + 1,
    Calendar[Year]
)

To handle the special case for Fiscal Year 2024, try the below code:

FYTD_Sales = 
VAR CurrentFiscalYear = MAX('Calendar'[FiscalYear])
RETURN
IF(
    CurrentFiscalYear = 2024,
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Calendar'),
            'Calendar'[FiscalYear] = 2024 &&
            'Calendar'[Date] <= MAX('Calendar'[Date])
        )
    ),
    CALCULATE(
        SUM('Table'[Value]),
        DATESYTD('Calendar'[Date], "31/10")
    )
)

I have relationship one to many from calendar to 'Table' using column Date.

Here is the output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz