formula to filter out fiscal YTD?

[ryanzhang487]

hello Community, I have a [Date] column with all the dates value, my fiscal year is beginning of Oct to End of Sept, I want to set up a filter for fiscal Year to Date (in this year case, it is since 2023-Oct-1) and set it to true,

what will be the formula for that?

Thank you for your help

[Raj]

@ryanzhang487 when you put filter on date field you have between option for start and end date>

the format you displayed is not a Alteryx date field 
kindly check if it is date then you can use the between option from Alteryx Filter tool.

[ryanzhang487]

Thanks, but I will need a dynamic fiscal YTD, If i put a start date in filter that would be static not dynamic

[Raj]

@ryanzhang487 Sharing Formula for Dynamic.
Please confirm you fiscal year is oct last year to sept this year

[ryanzhang487]

yes correct

[aatalai]

If DateTimeMonth(DateTimeNow())>9 then todate("DateTimeYear(DateTimeNow())-09-31"))

else  todate(  "tostring(tonumber((DateTimeYear(DateTimeNow())-1)))-09-31)")

[ryanzhang487]

thanks, it seems it does not work, assuming my column is [DATE], there is no column in the formula

[aatalai]

That formula is to get to the most recent fiscal year

[Raj · Accepted solution]

@ryanzhang487 find the formual attached.

[DateField] > IF DateTimeMonth(DateTimeToday()) > 9 THEN
DateTimeFormat(ToString(DateTimeYear(DateTimeNow())) + "-09-30","%Y-%m-%d")
ELSE
DateTimeFormat(ToString(DateTimeYear(DateTimeAdd(DateTimeNow(), -1, "years"))) + "-09-30","%Y-%m-%d")
ENDIF

[Raj]

Please mark it as done and like the solution if this was the required output.