Need help with changing the fiscal year based on the period

[punamraj]

My fiscal year starts in October. 

My date format looks like this  2024-10-01.

I used the formula below to move October to December by a year 

IF DateTimeMonth([Value Date]) >= 10 AND DateTimeMonth([Value Date]) <= 12 THEN
DateTimeAdd([Value Date], 1, 'years')
ELSE
DateTimeAdd([Value Date], 0, 'years')
ENDIF

How do I get Alteryx to recognize that Jan and Feb have also gone by this year and they have to show 2026?

Data set

Present	Value Date	New Date
	2024-10-01	2025-10-01	ok
	2025-01-01	2025-01-01	not ok
Needed	Value Date	New Date
	2024-10-01	2025-10-01	ok
	2025-01-01	2026-01-01	ok

[KGT]

So, anything before today should be next year? Editing your formula from above to get any dates between the start of FY and today...

IF [Value Date] <= DateTimeToday() AND [Value Date] > "2024-10-01" // This could be updated to be dynamic if needed

THEN
DateTimeAdd([Value Date], 1, 'years')
ELSE
DateTimeAdd([Value Date], 0, 'years')
ENDIF

[punamraj]

It almost worked but it changes the current month as well. March also shows 2026-03-01. I want it only till Feb

[KGT]

So, if you only want it to apply before the current month, then DateTimeToday() needs to be either:

• The last day of last month with "<=": DateTimeLastOfMonth(DateTimeAdd(DateTimeToday(),-1,'month'))
• The first day of this month with "<": DateTimeFirstOfMonth(DateTimeToday())

[punamraj]

Sorry. I am very new to the tool.

When I tried this one - even though March also moved a year - it ran without an error

IF [Value Date] <= DateTimeToday() AND [Value Date] > "2024-10-01" // This could be updated to be dynamic if needed

THEN
DateTimeAdd([Value Date], 1, 'years')
ELSE
DateTimeAdd([Value Date], 0, 'years')
ENDIF

When I try 

IF [Value Date] <= DateTimeLastOfMonth(DateTimeAdd(DateTimeToday(),-1,'month'))

AND [Value Date] > "2024-10-01"
THEN
DateTimeAdd([Value Date], 1, 'years')
ELSE
DateTimeAdd([Value Date], 0, 'years')
ENDIF

the error I get is  Formula (3) Parse Error at char(19): Wrong number of parameters for the function "DATETIMELASTOFMONTH". (Expression #7)

Same error with the second option as well

IF [Value Date] < DateTimeFirstOfMonth(DateTimeToday())

AND [Value Date] > "2024-10-01"
THEN
DateTimeAdd([Value Date], 1, 'years')
ELSE
DateTimeAdd([Value Date], 0, 'years')
ENDIF

[KGT]

Ah, my mistake, DateTimeLastOfMonth()/DateTimeFirstOfMonth() works on the current date already.

So, it's a lot simpler for the first condition: [Value Date] < DateTimeFirstOfMonth() 

[punamraj]

This ran without error but moved March also to 2026-03-01

[KGT]

OK, does it move the 2nd march? Or just the first March? It could just be that the value date (2025-03-01) is measuring as earlier than the DateTimeFirstOfMonth() which may be 2025-03-01 00:00:00. 

Try to wrap the DasteTimeFirstOfMonth() in Left([field],10) or create that as a field and then reference that field to test.